如何在 SQL Server 中查找所有数据库中所有表的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2729126/
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
How to find column names for all tables in all databases in SQL Server
提问by Salman A
I want to find all column names in all tables in all databases. Is there a query that can do that for me? The database is Microsoft SQL Server 2000.
我想在所有数据库的所有表中查找所有列名。有没有可以为我做的查询？数据库是 Microsoft SQL Server 2000。
回答by KM.
Try this:
尝试这个：
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
With resulting column names this would be:
使用结果列名称，这将是：
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
Or for more detail:
或了解更多详情：
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
EDIT
Here is a basic example to get all columns in all databases:
编辑
这是获取所有数据库中所有列的基本示例：
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)
EDITSQL Server 2000 version
编辑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)
EDIT
Based on some comments, here is a version using sp_MSforeachdb:
编辑
根据一些评论，这是一个使用的版本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'
回答by Jeremy
Why not use
为什么不使用
Select * From INFORMATION_SCHEMA.COLUMNS
You can make it DB specific with
你可以使它特定于数据库
Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS
回答by Mehran
SELECT *
FROM information_schema.columns
WHERE column_name = 'My_Column'
You must set your current database name with USE [db_name]before this query.
您必须USE [db_name]在此查询之前设置您当前的数据库名称。
回答by user5576010
Better way for you
更适合你的方式
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%'''
回答by majestzim
Normally I try to do whatever I can to avoid the use of cursors, but the following query will get you everything you need:
通常我会尽量避免使用游标，但以下查询将为您提供所需的一切：
--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
I added a where clause in the main query (ex: B.TABLE_NAME LIKE ''%%'' AND B.COLUMN_NAME LIKE ''%%'') so that you can search for specific tables and/or columns if you want to.
我在主查询中添加了一个 where 子句（例如：B.TABLE_NAME LIKE ''%%'' AND B.COLUMN_NAME LIKE ''%%''），以便您可以搜索特定的表和/或列，如果你想.
回答by Hank Freeman
To all: Thanks for all the post and comments some are good, but some are better.
致所有人：感谢所有的帖子和评论，有些是好的，但有些是更好的。
The first big script is good because it is delivers just what is needed. The fastest and most detailed is the one suggestion for selecting from INFORMATION_SCHEMA.COLUMNS..
第一个大脚本很好，因为它提供了所需的内容。最快和最详细的是从 INFORMATION_SCHEMA.COLUMNS 中选择的一个建议..
My need was to find all the errant columns of approximately the same name and Several databases.. Sooo, I made my versions of both (see below) ...Either of these two below script work and deliver the goods in seconds.
我的需要是找到几乎相同名称的所有错误列和几个数据库.. Sooo，我制作了我的两个版本（见下文）......下面这两个脚本中的任何一个都可以工作并在几秒钟内交付货物。
The assumption in other posts on this link, is that the first code example can be used successfully with for-each-database, is to me, not desirable. This is because the information is within the specific database and the simple use of the "fedb" doesn't produce the correct results, it simply doesn't give access. SOOO to that is why I use a CURSOR to collect the databases and ignore those that are Off-line, which in this case, a utility script, it is a good use of same.
此链接上的其他帖子中的假设是，第一个代码示例可以成功地与 for-each-database 一起使用，这对我来说是不可取的。这是因为信息在特定的数据库中，简单地使用“fedb”不会产生正确的结果，它只是不提供访问权限。SOOO 这就是为什么我使用 CURSOR 来收集数据库并忽略那些离线的，在这种情况下，这是一个实用程序脚本，它是一个很好的用途。
Bottom Line, I read everyone's post, incorporated all the correction from the posts and made what are two very eloquent scripts from others good works. I listed both below and have also placed the script file on my public folder at OneDrive.com which you can access with this link: http://1drv.ms/1vr8yNX
底线，我阅读了每个人的帖子，合并了帖子中的所有更正，并从其他人的好作品中制作了两个非常雄辩的脚本。我在下面列出了两个脚本文件，并将脚本文件放在 OneDrive.com 的公共文件夹中，您可以通过以下链接访问：http: //1drv.ms/1vr8yNX
Enjoy ! Hank Freeman
享受 ！汉克·弗里曼
Senior Level - SQL Server DBA - Data Architect
高级 - SQL Server DBA - 数据架构师
Try them separately...
分别尝试...
---------------------------
--- 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
--------
回答by Valderann
Some minor improvements
一些小的改进
->previous answers weren't showing all results
->以前的答案没有显示所有结果
->possible to filter on column name by setting the column name variable
-> 可以通过设置列名变量来过滤列名
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)
回答by Ardalan Shahgholi
user @KM say best Answer.
用户@KM 说最好的答案。
I Use This :
我用这个：
Declare @Table_Name VarChar(100) ,@Column_Name VarChar(100)
Set @Table_Name = ''
Set @Column_Name = ''
Select
RowNumber = Row_Number() Over( PARTITION BY T.[Name] Order By T.[Name],C.column_id ),
SCHEMA_NAME( T.schema_id ) As SchemaName ,
T.[Name] As Table_Name ,
C.[Name] As Field_Name ,
sysType.name ,
C.max_length , C.is_nullable , C.is_identity , C.scale , C.precision
From Sys.Tables As T
Left Join Sys.Columns As C On ( T.[Object_Id] = C.[Object_Id] )
Left Join sys.types As sysType On ( C.user_type_id = sysType.user_type_id )
Where ( Type = 'U' )
And ( C.Name Like '%' + @Column_Name + '%' )
And ( T.Name Like '%' + @Table_Name + '%' )
回答by Nandish B
try the below query
试试下面的查询
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
gives list of tables containing ID column from all databases.
给出包含来自所有数据库的 ID 列的表列表。
回答by Samuel Nde
I just realized that the following query would give you all column names from the table in your database (SQL SERVER 2017)
我刚刚意识到以下查询将为您提供数据库表中的所有列名（SQL SERVER 2017）
SELECT DISTINCT NAME FROM SYSCOLUMNS
ORDER BY Name
OR SIMPLY
或者简单地
SELECT Name FROM SYSCOLUMNS
If you do not care about duplicated names.
如果你不关心重名。
Another option is SELECT Column names from INFORMATION_SCHEMA
另一个选项是 SELECT Column names from INFORMATION_SCHEMA
SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY column_name
It is usually more interesting to have the TableNameas well as the ColumnNameant the query below does just that.
拥有TableName和ColumnName通常更有趣，下面的查询就是这样做的。
SELECT
Object_Name(Id) As TableName,
Name As ColumnName
FROM SysColumns
And the results would look like
结果看起来像
TableName ColumnName
0 Table1 column11
1 Table1 Column12
2 Table2 Column21
3 Table2 Column22
4 Table3 Column23
