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 query that can do that for me?

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

Answer 2

Why not use

Select * From INFORMATION_SCHEMA.COLUMNS 

You can make it DB specific with

Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS