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.
+5
A:
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
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.system_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 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)
EDIT SQL Server 2000 version
DECLARE @SQL varchar(max)
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 aversion 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'
KM
2010-04-28 11:58:12
Works on current database, not all databases.
Salman A
2010-04-28 12:02:33
@marc_s, not true, see my latest edit. if you use dynamic sql, you can build a UNION ALL query containing a select for each database.
KM
2010-04-28 12:19:06
Can you port this example for SQL Server 2000? In SQL Server 2000 there is no `sys` object. Would replacing `sys.colums` with `syscolumns` help?
Salman A
2010-04-28 12:33:36
@Salman A, See latest edit for SQl Server 2000 version
KM
2010-04-28 12:52:01
You could also try running the original query with sp_MSforeachdb
Chris W
2010-04-28 12:54:37
Thanks. I got it to work!
Salman A
2010-04-28 12:56:24
As Chris W said, although they are undocumented, sp_msforeachdb and sp_msforeachtable would be ideal here. Check http://blogs.techrepublic.com.com/datacenter/?p=395 for some details.
Matt Gibson
2010-04-28 13:14:56
A:
Based on KM's answer, here is the cleaned up version for SQL Server 2000:
DECLARE @SQL VARCHAR(8000)
SELECT @SQL = ''
SELECT @SQL = @SQL + 'UNION
SELECT ''' + name + ''' [DB], t.name [TBL], c.name [COL] FROM ' + name + '..sysobjects t INNER JOIN ' + name + '..syscolumns c ON t.id = c.id '
FROM master..sysdatabases
WHERE name IN ('Database1', 'Database2', 'Database3')
SELECT @SQL = RIGHT(@SQL, LEN(@SQL)-5) + ' ORDER BY 1, 2, 3'
EXEC(@SQL)
Salman A
2010-04-28 13:03:52
A:
Why not use
Select * From INFORMATION_SCHEMA.COLUMNS
You can make it DB specific with
Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS
Jeremy
2010-04-28 13:12:40