List of columns from tables across databases.

[OhMyGaw]

Hey guys,

Couldn't find this anywhere in google.

I want a list of all database column names for a specific table/view
from across database.

I tried this...
-----------------------------------------------------
Select *

From Information_Sch ema.Columns

-----------------------------------------------------

I also tried this...

-----------------------------------------------------
select syscolumns.name , sysobjects.name , * from syscolumns, sysobjects
where
sysobjects.id = syscolumns.id
and (sysobjects.xty pe='U' or sysobjects.xtyp e='S')
-----------------------------------------------------

These queries return information about the CURRENT database.

But, if I want to do it ACROSS database or across servers.. how can I
do this?

I will express my gratitude to everyone who is kind enough to answer
this question. (I've been stuck with this problem for a while now.)

Thanks!

OhMyGaw!

[David Portas]

Query other databases using the three-part name:

SELECT *
FROM database_name.i nformation_sche ma.columns

SELECT C.name, O.name, *
FROM database_name.d bo.syscolumns AS C,
database_name.d bo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.dat abase_name.info rmation_schema. columns

SELECT C.name, O.name, *
FROM server_name.dat abase_name.dbo. syscolumns AS C,
server_name.dat abase_name.dbo. sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--

[David Portas]

Query other databases using the three-part name:

SELECT *
FROM database_name.i nformation_sche ma.columns

SELECT C.name, O.name, *
FROM database_name.d bo.syscolumns AS C,
database_name.d bo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.dat abase_name.info rmation_schema. columns

SELECT C.name, O.name, *
FROM server_name.dat abase_name.dbo. syscolumns AS C,
server_name.dat abase_name.dbo. sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--

[OhMyGaw]

David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.i nformation_sche *ma.columns

I was trying the following

SELECT *
FROM database_name.d atabase_owner.i nformation_sche *ma.columns

BTW, where is this information_sch ema table? I couldn't find it when
I looked for it.

Thanks a bunch.

[OhMyGaw]

David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.i nformation_sche *ma.columns

I was trying the following

SELECT *
FROM database_name.d atabase_owner.i nformation_sche *ma.columns

BTW, where is this information_sch ema table? I couldn't find it when
I looked for it.

Thanks a bunch.

[David Portas]

Information_sch ema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_sch ema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--

[David Portas]

Information_sch ema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_sch ema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--