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_Schema.Columns
----------------
I also tried this...
----------------
select syscolumns.name, sysobjects.name, * from syscolumns, sysobjects
where
sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='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!Query other databases using the three-part name:
SELECT *
FROM database_name.information_schema.columns
SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.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.database_name.information_schema.colum ns
SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_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
--|||Query other databases using the three-part name:
SELECT *
FROM database_name.information_schema.columns
SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.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.database_name.information_schema.colum ns
SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_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,
Thanks for your response. This is exactly what I was looking for.
SELECT *
FROM database_name.information_sche*ma.columns
I was trying the following
SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns
BTW, where is this information_schema table? I couldn't find it when
I looked for it.
Thanks a bunch.|||David,
Thanks for your response. This is exactly what I was looking for.
SELECT *
FROM database_name.information_sche*ma.columns
I was trying the following
SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns
BTW, where is this information_schema table? I couldn't find it when
I looked for it.
Thanks a bunch.|||Information_schema 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_schema 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
--|||Information_schema 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_schema 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
--
