SQL Server 按名称搜索列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3761673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server search for a column by name
提问by EJC
I'm doing some recon work and having to dig through a few hundred SQL Server database tables to find columns.
我正在做一些侦察工作，不得不通过几百个 SQL Server 数据库表来查找列。
Is there a way to easily search for columns in the database and return just the table name that the column belongs to?
有没有一种方法可以轻松搜索数据库中的列并仅返回该列所属的表名？
I found this, but that also returns Stored procedures with that column name in it...
我找到了这个，但它也会返回包含该列名的存储过程......
回答by gbn
SELECT OBJECT_NAME(object_id) FROM sys.columns WHERE name = 'foo'
This includes views though but can be further filtered . It may be useful though.
这虽然包括视图，但可以进一步过滤。不过它可能有用。
More generally...
更普遍...
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'foo'
回答by KM.
To get the
为了得到
1) full column name
2) object name (including schema)
3) object type (table/view)
4) data type (nice format: varchar(6) or numeric(5,2), etc.)
5) null/not null
6) information on identity, check constraint, and default info
1) 完整列名
2) 对象名（包括架构）
3） 对象类型（表/视图）
4） 数据类型（不错的格式：varchar(6) 或 numeric(5,2) 等）
5) null/not null
6) 关于身份、检查约束和默认信息的信息
try this:
尝试这个：
DECLARE @Search varchar(200)
SET @Search='YourColumnName' --can be a partial or a complete name
SELECT
s.name as ColumnName
,sh.name+'.'+o.name AS ObjectName
,o.type_desc AS ObjectType
,CASE
WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
ELSE t.name
END AS DataType
,CASE
WHEN s.is_nullable=1 THEN 'NULL'
ELSE 'NOT NULL'
END AS Nullable
,CASE
WHEN ic.column_id IS NULL THEN ''
ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
END
+CASE
WHEN sc.column_id IS NULL THEN ''
ELSE ' computed('+ISNULL(sc.definition,'')+')'
END
+CASE
WHEN cc.object_id IS NULL THEN ''
ELSE ' check('+ISNULL(cc.definition,'')+')'
END
AS MiscInfo
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.system_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
LEFT OUTER JOIN sys.identity_columns ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
LEFT OUTER JOIN sys.computed_columns sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
WHERE s.name LIKE '%'+@Search+'%'
回答by RedFilter
select c.name as ColumnName, o.name as TableName
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
where c.name = 'MyColumnName'
回答by Scott
This should do it for you. I use it everyday.
这应该为你做。我每天都使用它。
回答by Kilanash
select table_name from information_schema.columns
where column_name = '<your column name here>'
Using the information_schema views is 'more correct' as system details in the system databases are subject to change between implementations of SQL Server.
使用 information_schema 视图“更正确”，因为系统数据库中的系统详细信息可能会在 SQL Server 的实现之间发生变化。
