By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

List of columns from tables across databases.

P: n/a
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_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!

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
--
Jul 23 '05 #2

P: n/a
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
--
Jul 23 '05 #3

P: n/a
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.

Jul 23 '05 #4

P: n/a
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.

Jul 23 '05 #5

P: n/a
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
--

Jul 23 '05 #6

P: n/a
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
--

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.