我想找出所有数据库中所有表格的列名。有没有一种查询可以为我完成这个任务？
如何在SQL Server中找到所有数据库中所有表的列名
87
- Salman A
1
1我已经更新了我的答案，现在它适用于 SQL Server 2000 上的所有数据库。 - KM.
16个回答
120
试试这个：
select
o.name,c.name
from sys.columns c
inner join sys.objects o on c.object_id=o.object_id
order by o.name,c.column_id
使用结果列名，这将是：
select
o.name as [Table], c.name as [Column]
from sys.columns c
inner join sys.objects o on c.object_id=o.object_id
--where c.name = 'column you want to find'
order by o.name,c.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.user_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
ORDER BY sh.name+'.'+o.name,s.column_id
编辑
这是一个获取所有数据库中所有列的基本示例：
DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.column_id
from '+d.name+'.sys.columns c
inner join '+d.name+'.sys.objects o on c.object_id=o.object_id
INNER JOIN '+d.name+'.sys.schemas sh on o.schema_id=sh.schema_id
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
编辑 SQL Server 2000 版本
DECLARE @SQL varchar(8000)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.colid
from '+d.name+'..syscolumns c
inner join sysobjects o on c.id=o.id
INNER JOIN sysusers sh on o.uid=sh.uid
'
FROM master.dbo.sysdatabases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
编辑
根据一些评论，这里提供一个使用sp_MSforeachdb的版本:
sp_MSforeachdb 'select
''?'' AS DatabaseName, o.name AS TableName,c.name AS ColumnName
from sys.columns c
inner join ?.sys.objects o on c.object_id=o.object_id
--WHERE ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')
order by o.name,c.column_id'
- KM.
17
2你能将这个示例移植到SQL Server 2000吗？在SQL Server 2000中没有
sys对象，用syscolumns替换sys.colums会有帮助吗？ - Salman A2您也可以尝试使用sp_MSforeachdb运行原始查询。 - Chris W
2正如Chris W所说，虽然它们没有文档记录，但sp_msforeachdb和sp_msforeachtable在这里是理想的选择。请查看http://blogs.techrepublic.com.com/datacenter/?p=395获取更多详细信息。 - Matt Gibson
2@Chipmonkey，我修复了那个bug。我将
inner join sys.objects更改为inner join ?.sys.objects。如果你的名称中有特殊字符，请随意添加方括号。 - KM.2@Davos，感谢您的建议，如果遇到问题，确实可以采用这种方法来解决。说实话，我很讨厌方括号，如果你需要使用它们，说明你在为项目命名时犯了错误。 - KM.
显示剩余12条评论
36
为什么不使用
Select * From INFORMATION_SCHEMA.COLUMNS
你可以使用以下方式使其特定于数据库：
Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS
- Jeremy
4
有一个原因是你想在一个函数内部执行此操作，但该函数事先不知道数据库的名称。 - Zach Smith
2所以问题是永恒的，因为这是高排名的谷歌搜索结果。另外，我不知道那个。 - Zach Smith
SQL Server 2000除了所有内置的标量函数之外，还有
OPENQUERY()和动态SQL，例如EXEC（'sql string here'），这些都是在不提前知道DBNAME的情况下有效使用的案例。请查阅文档https://www.microsoft.com/en-us/download/details.aspx?id=51958。这个答案对于某些用例来说足够简单，但公平地说，您将您的答案作为问题提出，无论是修辞的还是刻意的，都存在着不使用它的有效理由。 - Davos我猜5年前Zach没有向您清楚地解释这个查询的问题。问题要求如何跨所有数据库获取信息，而您的答案是特定于数据库的——第一个将查询当前数据库，第二个将查询命名的数据库。如果您只有一个数据库，那么这样做是可以的，但如果您有多个数据库，则无法正常工作。 - Hogan
27
SELECT *
FROM information_schema.columns
WHERE column_name = 'My_Column'
在执行此查询之前，您必须使用 USE [db_name] 设置当前数据库名称。
- Mehran
1
9问题特别强调需要涵盖“所有数据库”，而不仅仅是其中一种。 - Nikhil Girraj
10
让你更好的方式
sp_MSForEachDB @command1='USE ?;
SELECT
Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,Data_Type
,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ''%ColumnNameHere%'''
- user5576010
5
我不太喜欢为每个数据库获取单独的结果集。 - jumxozizi
2这是唯一一个对我有效的版本。KM的版本只返回系统表中的列。此外，必须在USE语句中的?周围加上[]以覆盖带有空格的数据库名称。 - Rocky
很好，user5576010。在所有贴出的解决方案中，这个似乎非常全面。 - ASH
太好了。我在“？”周围加上了方括号，以避免在某些数据库名称上出现错误。 - cloudsafe
4
一些小改进
->之前的答案没有显示所有结果
->可以通过设置列名变量来过滤列名
DECLARE @columnname nvarchar(150)
SET @columnname=''
DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
SELECT
'''+d.name+'.''+sh.name+''.''+o.name COLLATE SQL_Latin1_General_CP1_CI_AS as name,c.name COLLATE SQL_Latin1_General_CP1_CI_AS as columnname,c.column_id
FROM '+d.name+'.sys.columns c
INNER JOIN '+d.name+'.sys.objects o on c.object_id=o.object_id
INNER JOIN '+d.name+'.sys.schemas sh on o.schema_id=sh.schema_id
WHERE c.name like ''%'+@columnname+'%'' AND sh.name<>''sys''
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
- Valderann
1
你不需要使用"union"，只用"union all"就足够了。https://dev59.com/qnVD5IYBdhLWcg3wNY1Z - jumxozizi
3
致所有人：感谢你们所有的帖子和评论，有些很好，但有些更好。
第一个大脚本很好，因为它提供了所需的内容。选择 INFORMATION_SCHEMA.COLUMNS 中最快且最详细的建议是最好的。
我的需求是找到所有名称相似的错误列和几个数据库。所以，我制作了自己的版本（见下文）......这两个脚本中的任何一个都可以工作，并在几秒钟内提供所需的信息。
其他链接上的其他帖子的假设是，第一个代码示例可以成功地与 for-each-database 一起使用，对我来说并不理想。这是因为信息包含在特定数据库中，而简单使用 "fedb" 并不能产生正确的结果，它只是没有访问权限。所以，我使用游标收集数据库并忽略那些离线的数据库，在这种情况下，它是一个好用途的实用脚本。
底线是，我阅读了每个人的帖子，结合了所有的纠正意见，并从其他人的优秀作品中制作了两个非常优美的脚本。我列出了两者，还将脚本文件放在 OneDrive.com 上的公共文件夹中，您可以通过此链接访问：http://1drv.ms/1vr8yNX 享受吧！ Hank Freeman
高级 SQL Server DBA - 数据架构师
分别尝试它们...
第一个大脚本很好，因为它提供了所需的内容。选择 INFORMATION_SCHEMA.COLUMNS 中最快且最详细的建议是最好的。
我的需求是找到所有名称相似的错误列和几个数据库。所以，我制作了自己的版本（见下文）......这两个脚本中的任何一个都可以工作，并在几秒钟内提供所需的信息。
其他链接上的其他帖子的假设是，第一个代码示例可以成功地与 for-each-database 一起使用，对我来说并不理想。这是因为信息包含在特定数据库中，而简单使用 "fedb" 并不能产生正确的结果，它只是没有访问权限。所以，我使用游标收集数据库并忽略那些离线的数据库，在这种情况下，它是一个好用途的实用脚本。
底线是，我阅读了每个人的帖子，结合了所有的纠正意见，并从其他人的优秀作品中制作了两个非常优美的脚本。我列出了两者，还将脚本文件放在 OneDrive.com 上的公共文件夹中，您可以通过此链接访问：http://1drv.ms/1vr8yNX 享受吧！ Hank Freeman
高级 SQL Server DBA - 数据架构师
分别尝试它们...
---------------------------
--- 1st example (works) ---
---------------------------
Declare
@DBName sysname
,@SQL_String1 nvarchar(4000)
,@SQL_String2 nvarchar(4000)
,@ColumnName nvarchar(200)
--set @ColumnName = 'Course_ID'
-------- Like Trick --------
-- IF you want to add more the @ColumnName so it looks like Course_ID,CourseID
-- then add an additional pairing of +''','''+'NewColumnSearchIDValue'
----------------------------
set @ColumnName = 'Course_ID' +''','''+'CourseID'
--select @ColumnName
-----
Declare @Column_Info table
(
[DatabaseName] nvarchar(128) NULL,
[ColumnName] sysname NULL,
[ObjectName] nvarchar(257) NOT NULL,
[ObjectType] nvarchar(60) NULL,
[DataType] nvarchar(151) NULL,
[Nullable] varchar(8) NOT NULL,
[MiscInfo] nvarchar(MAX) NOT NULL
)
--------------
Begin
set @SQL_String2 = 'SELECT
DB_NAME() as ''DatabaseName'',
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
into ##Temp_Column_Info
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_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
--------------------------------------------
--- DBA - Hank 12-Feb-2015 added this specific where statement
-- where Upper(s.name) like ''COURSE%''
-- where Upper(s.name) in (''' + @ColumnName + ''')
-- where Upper(s.name) in (''cycle_Code'')
-- ORDER BY sh.name+''.''+o.name,s.column_id
order by 1,2'
--------------------
Declare DB_cursor CURSOR
FOR
SELECT name FROM sys.databases
--select * from sys.databases
WHERE STATE = 0
-- and Name not IN ('master','msdb','tempdb','model','DocxPress')
and Name not IN ('msdb','tempdb','model','DocxPress')
Open DB_cursor
Fetch next from DB_cursor into @DBName
While @@FETCH_STATUS = 0
begin
--select @DBName as '@DBName';
Set @SQL_String1 = 'USE [' + @DBName + ']'
set @SQL_String1 = @SQL_String1 + @SQL_String2
EXEC sp_executesql @SQL_String1;
--
insert into @Column_Info
select * from ##Temp_Column_Info;
drop table ##Temp_Column_Info;
Fetch next From DB_cursor into @DBName
end
CLOSE DB_cursor;
Deallocate DB_cursor;
---
select * from @Column_Info order by 2,3
----------------------------
end
---------------------------
Below is the Second script..
---------------------------
--- 2nd example (works) ---
---------------------------
-- This is by far the best/fastes of the lot for what it delivers.
--Select * into dbo.hanktst From Master.INFORMATION_SCHEMA.COLUMNS
--FileID: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
----------------------------------------
--FileID: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
-- Utility to find all columns in all databases or find specific with a like statement
-- Look at this line to find a: --> set @SQL_String2 = ' select * into ##Temp_Column_Info....
----------------------------------------
---
SET NOCOUNT ON
begin
Declare @hanktst TABLE (
[TABLE_CATALOG] NVARCHAR(128) NULL
,[TABLE_SCHEMA] NVARCHAR(128) NULL
,[TABLE_NAME] sysname NOT NULL
,[COLUMN_NAME] sysname NULL
,[ORDINAL_POSITION] INT NULL
,[COLUMN_DEFAULT] NVARCHAR(4000) NULL
,[IS_NULLABLE] VARCHAR(3) NULL
,[DATA_TYPE] NVARCHAR(128) NULL
,[CHARACTER_MAXIMUM_LENGTH] INT NULL
,[CHARACTER_OCTET_LENGTH] INT NULL
,[NUMERIC_PRECISION] TINYINT NULL
,[NUMERIC_PRECISION_RADIX] SMALLINT NULL
,[NUMERIC_SCALE] INT NULL
,[DATETIME_PRECISION] SMALLINT NULL
,[CHARACTER_SET_CATALOG] sysname NULL
,[CHARACTER_SET_SCHEMA] sysname NULL
,[CHARACTER_SET_NAME] sysname NULL
,[COLLATION_CATALOG] sysname NULL
,[COLLATION_SCHEMA] sysname NULL
,[COLLATION_NAME] sysname NULL
,[DOMAIN_CATALOG] sysname NULL
,[DOMAIN_SCHEMA] sysname NULL
,[DOMAIN_NAME] sysname NULL
)
Declare
@DBName sysname
,@SQL_String2 nvarchar(4000)
,@TempRowCnt varchar(20)
,@Dbug bit = 0
Declare DB_cursor CURSOR
FOR
SELECT name FROM sys.databases
WHERE STATE = 0
-- and Name not IN ('master','msdb','tempdb','model','DocxPress')
and Name not IN ('msdb','tempdb','model','DocxPress')
Open DB_cursor
Fetch next from DB_cursor into @DBName
While @@FETCH_STATUS = 0
begin
set @SQL_String2 = ' select * into ##Temp_Column_Info from [' + @DBName + '].INFORMATION_SCHEMA.COLUMNS
where UPPER(Column_Name) like ''COURSE%''
;'
if @Dbug = 1 Select @SQL_String2 as '@SQL_String2';
EXEC sp_executesql @SQL_String2;
insert into @hanktst
select * from ##Temp_Column_Info;
drop table ##Temp_Column_Info;
Fetch next From DB_cursor into @DBName
end
select * from @hanktst order by 4,2,3
CLOSE DB_cursor;
Deallocate DB_cursor;
set @TempRowCnt = (select cast(count(1) as varchar(10)) from @hanktst )
Print ('Rows found: '+ @TempRowCnt +' end ...')
end
--------
- Hank Freeman
1
答案越长并不意味着越好。此外，请保持缩进和格式的一致性——这很混乱。 - Hogan
3
我刚刚意识到，以下查询可以从你的数据库表中获取所有列名（SQL SERVER 2017）
SELECT DISTINCT NAME FROM SYSCOLUMNS
ORDER BY Name
或者简单地说
SELECT Name FROM SYSCOLUMNS
如果您不在意重复的名称，
另一个选择是从
INFORMATION_SCHEMA中选择列名。SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY column_name
通常情况下，将表名和列名同时显示更为有趣，以下查询正是如此。
SELECT
Object_Name(Id) As TableName,
Name As ColumnName
FROM SysColumns
而且结果看起来会像这样：
TableName ColumnName
0 Table1 column11
1 Table1 Column12
2 Table2 Column21
3 Table2 Column22
4 Table3 Column23
- Samuel Nde
1
这些都没有回答问题——即如何在多个数据库中进行搜索。 - Hogan
3
通常我尽量避免使用游标，但以下查询将为您提供所需的一切：
--Declare/Set required variables
DECLARE @vchDynamicDatabaseName AS VARCHAR(MAX),
@vchDynamicQuery As VARCHAR(MAX),
@DatabasesCursor CURSOR
SET @DatabasesCursor = Cursor FOR
--Select * useful databases on the server
SELECT name
FROM sys.databases
WHERE database_id > 4
ORDER by name
--Open the Cursor based on the previous select
OPEN @DatabasesCursor
FETCH NEXT FROM @DatabasesCursor INTO @vchDynamicDatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert the select statement into @DynamicQuery
--This query will select the Database name, all tables/views and their columns (in a comma delimited field)
SET @vchDynamicQuery =
('SELECT ''' + @vchDynamicDatabaseName + ''' AS ''Database_Name'',
B.table_name AS ''Table Name'',
STUFF((SELECT '', '' + A.column_name
FROM ' + @vchDynamicDatabaseName + '.INFORMATION_SCHEMA.COLUMNS A
WHERE A.Table_name = B.Table_Name
FOR XML PATH(''''),TYPE).value(''(./text())[1]'',''NVARCHAR(MAX)'')
, 1, 2, '''') AS ''Columns''
FROM ' + @vchDynamicDatabaseName + '.INFORMATION_SCHEMA.COLUMNS B
WHERE B.TABLE_NAME LIKE ''%%''
AND B.COLUMN_NAME LIKE ''%%''
GROUP BY B.Table_Name
Order BY 1 ASC')
--Print @vchDynamicQuery
EXEC(@vchDynamicQuery)
FETCH NEXT FROM @DatabasesCursor INTO @vchDynamicDatabaseName
END
CLOSE @DatabasesCursor
DEALLOCATE @DatabasesCursor
GO
我在主查询中添加了一个where子句（例如：B.TABLE_NAME LIKE ''%%'' AND B.COLUMN_NAME LIKE ''%%''），这样你就可以搜索特定的表和/或列。
- majestzim
3
我使用了：
EXEC sp_MSforeachdb 'Use ? Select * From INFORMATION_SCHEMA.COLUMNS '
看起来对我所需的工作。
- Max Wright
2
尝试下面的查询
DECLARE @Query VARCHAR(max)
SELECT @Query = 'USE ? SELECT ''?'' AS DataBaseName,
sys.columns.name AS ColumnName ,
sys.tables.name AS TableName ,
schema_name (sys.tables.schema_Id) AS schemaName
FROM sys.columns
JOIN sys.tables
ON sys.columns.object_id = sys.tables.object_id
WHERE sys.columns.name = ''id'' '
EXEC SP_MSFOREACHDB @Query
提供包含所有数据库中 ID 列的表列表。
- Nandish B
1
我很感兴趣——为什么要使用@Query变量——它并没有以任何方式增加答案，只会让它更加复杂。 - Hogan
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接