如何查找 SQLServer 中所有数据库中所有表的列名

我想找到所有表 在所有数据库里中的所有列名。有什么问题能帮到我吗？

224531 次浏览
小开
最佳答案

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)

EDIT SQL Server 2000 version

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 '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'
小开

Why not use

Select * From INFORMATION_SCHEMA.COLUMNS

You can make it DB specific with

Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS
小开 
SELECT *
FROM information_schema.columns
WHERE column_name = 'My_Column'

You must set your current database name with USE [db_name] before this query.

小开

Minor refinement on KM's solution for those like me who've got collation fun on their DB server....

DECLARE @SQL varchar(max)=''


SELECT @SQL=@SQL+'UNION
select
'''+d.name +'.''+sh.name+''.''+o.name COLLATE SQL_Latin1_General_CP1_CI_AS,c.name COLLATE SQL_Latin1_General_CP1_CI_AS,c.column_id
from '+d.name +'.sys.columns            c
inner join sys.objects  o on c.object_id=o.object_id
INNER JOIN 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)

(Still living in hope that we'll find a way to do this that can be wrapped into a view.)

小开

user @KM say best Answer.

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 + '%' )
小开

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.

小开

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..

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.

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.

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

Enjoy ! Hank Freeman

Senior Level - SQL Server DBA - Data Architect

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
--------
小开

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)
小开

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%'''
小开

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.

小开

I just realized that the following query would give you all column names from the table in your database (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 DISTINCT column_name  FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY column_name

It is usually more interesting to have the TableName as well as the ColumnName ant the query below does just that.

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
小开 
SELECT sys.columns.name AS ColumnName, tables.name AS TableName
FROM sys.columns
JOIN sys.tables ON sys.columns.object_id = tables.object_id
小开

I used:

EXEC sp_MSforeachdb 'Use ? Select * From INFORMATION_SCHEMA.COLUMNS '

It seems to work for what I needed.

小开

My solution for similar task was:

    if object_id('tempdb..##temp_meta') is not null
drop table ##temp_meta


create table ##temp_meta(database_name sysname
, schema_name sysname
, table_name sysname
, column_name sysname
, column_id tinyint
, type_name sysname
, max_length int
, precision tinyint
, scale tinyint
, is_nullable bit
, is_identity bit)


exec sp_MSforeachdb '


if(''?'' not in (''master'', ''msdb'', ''model'', ''tempdb''))
begin
USE [?]
insert into ##temp_meta
SELECT
''?'',
s.[name] AS schema_name,
t.name AS table_name,
c.[name] as column_name,
c.column_id,
ty.name as type_name,
c.max_length,
c.precision,
c.scale,
c.is_nullable,
c.is_identity
FROM sys.columns c
JOIN sys.types ty
ON ty.system_type_id = c.system_type_id
JOIN sys.tables t
ON c.object_id = t.object_id
JOIN sys.schemas s
ON s.schema_id = t.schema_id
        

end'
小开

sp_MSForEachDB @command1='USE ?;

SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE --Data_Type = ''int'' --AND COLUMN_NAME like ''%ColumnName%'' And Table_name like ''%TableName%'' order by 1,2,3 '

小开

Here, this is my code to search for a column name in all databases in the current instance:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      themah
-- Create date: 2022-10-09
-- Description: Looking for a column name in all databases in current instance, and list the db names
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[FindColumnNameInAllDatabases]
@columnNameToFind NVARCHAR (100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


DECLARE @dbName VARCHAR(100)
DECLARE @queryEachDatabase NVARCHAR(MAX)


DECLARE dbNamesCursor CURSOR FOR
SELECT [Name]
FROM sys.databases AS d
WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')
    

OPEN dbNamesCursor
FETCH NEXT FROM dbNamesCursor INTO @dbName


WHILE (@@FETCH_STATUS = 0)
BEGIN
    

SET @queryEachDatabase = N'IF Exists
(SELECT 1 FROM [' + @dbName + '].[INFORMATION_SCHEMA].[Columns] WHERE [Column_Name] Like N''%' + @columnNameToFind + '%'')
BEGIN
PRINT ''' + @dbName + '''
END
'
--PRINT @queryEachDatabase
EXEC(@queryEachDatabase)
    

FETCH NEXT FROM dbNamesCursor INTO @dbName
END


CLOSE dbNamesCursor
DEALLOCATE dbNamesCursor
    

END
GO

And a usage example : EXEC dbo.FindColumnNameInAllDb N'firstName'

Or another useful way to print the queries is this :

DECLARE @columnName NVARCHAR(100) = N'firstName'


SELECT
'SELECT * FROM [' + NAME + '].[INFORMATION_SCHEMA].[Columns] WHERE [Column_Name] Like N''%' + @columnName + '%'''
FROM [sys].[databases]

提交答案