How to find column names for all tables in all databases in SQL Server

Question

I want to find all column names in all tables in all databases . Is there a request that can do this for me? The database is Microsoft SQL Server 2000.

Answer 1

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 the resulting column names, this will 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 details:

 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 the 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' 

Answer 2

Why not use

 Select * From INFORMATION_SCHEMA.COLUMNS 

You can make it database specific with

 Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS 

Answer 3

 SELECT * FROM information_schema.columns WHERE column_name = 'My_Column' 

Before this query, you must set the current USE [db_name] database name USE [db_name] .

Answer 4

Best 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%''' 

Answer 5

@KM user says the 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 + '%' ) 

Answer 6

Usually I try to do my best to avoid using cursors, but the following query will give 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 (for example: B.TABLE_NAME LIKE '' %% '' AND B.COLUMN_NAME LIKE '' %% '') so that you can search for specific tables and / or columns if you want.

Answer 7

Everyone: Thanks for all the post and comments, some of them are good, but some of them are better.

The first big script is good because it delivers only what you need. The fastest and most detailed is one suggestion for choosing from INFORMATION_SCHEMA.COLUMNS ..

I needed to find all the erroneous columns with approximately the same name and several databases .. Sooo, I made my versions of both (see below) ... Any of the two below script works and delivers the goods in seconds.

The assumption in other posts on this link is that the first code sample can be successfully used for each database, which is undesirable for me. This is due to the fact that the information is within a specific database, and the simple use of "fedb" does not give the correct results, it simply does not give access. SOOO, so I use CURSOR to collect databases and ignore those that are Off-line, which in this case is a script utility, it's good to use.

Bottom Line, I read all the messages, included all the corrections from the messages, and did what two very eloquent scenarios from other good work. I listed both below, and also placed the script file in my shared folder on OneDrive.com, with which you can access this link: http://1drv.ms/1vr8yNX

Enjoy it! 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 -------- 

Answer 8

Some minor improvements

-> previous answers did not show all results

-> can be filtered by column name by specifying a 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) 

Answer 9

try the following 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 

contains a list of tables containing a column of identifiers from all databases.

Answer 10

I just realized that the following query will give you all the column names from a table in your database (SQL SERVER 2017)

 SELECT DISTINCT NAME FROM SYSCOLUMNS ORDER BY Name 

OR SIMPLY

 SELECT Name FROM SYSCOLUMNS 

Unless you care about duplicate 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 a TableName as well as a ColumnName, and the query below does just that.

 SELECT Object_Name(Id) As TableName, Name As ColumnName FROM SysColumns 

And the results will look

  TableName ColumnName 0 Table1 column11 1 Table1 Column12 2 Table2 Column21 3 Table2 Column22 4 Table3 Column23 

Answer 11

 SELECT sys.columns.name AS ColumnName, tables.name AS TableName FROM sys.columns JOIN sys.tables ON sys.columns.object_id = tables.object_id 

Answer 12

I used:

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

It seems to work for what I need.

Answer 13

A minor improvement to the KM solution for people like me who have sorting on their database 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) 

(We still live in the hope that we will find a way to do this that can be wrapped in a presentation.)