I want to find all column names in all tables in all databases. Is there a query that can do that for me?
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'
Can you port this example for SQL Server 2000? In SQL Server 2000 there is no
sys
object. Would replacingsys.colums
withsyscolumns
help?You could also try running the original query with sp_MSforeachdb
As Chris W said, although they are undocumented, sp_msforeachdb and sp_msforeachtable would be ideal here. Check blogs.techrepublic.com.com/datacenter/?p=395 for some details.
@Chipmonkey, I fixed that bug. I changed
inner join sys.objects
toinner join ?.sys.objects
. feel free to add square braces if you have special characters in your names.@Davos, thanks for the suggestions, if you run into problems, yes that is a good way to fix it. I hate square braces, if you need to use them you did something wrong naming your items.