How to read system tables for all DBs on a server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to read system tables for all DBs on a server

Author

Topic

bobk544
Starting Member

6 Posts

Posted - 2009-07-27 : 11:02:40

Hell, can anyone tell me how i can read all of the system tables for all databases on a server please?

I basically want to create report of all databases, all tables and all columns on a particular server.

Currently i can open a particular database and run this query, but i'd like to be able to loop thru all of the DBs and create this report in 1 run:
select so.name as soname, sc.name as scname, sc.length as sclen, sc.scale as scscale, sc.prec as scprec, st.name as stname
from syscolumns sc, systypes st, sysobjects so
where so.id = sc.id and sc.type = st.type

Thanks for any ideas!
BobK

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 11:15:55

hi

Try this...
EXEC DBO.SP_MSFOREACHDB 'USE ?;SELECT NAME FROM SYS.OBJECTS WHERE TYPE=''S'''

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 11:17:30

hI

CREATE TABLE #TEMP
(
TABLE_NAME SYSNAME
)

INSERT INTO #TEMP

EXEC DBO.SP_MSFOREACHDB 'USE ?;SELECT NAME FROM SYS.OBJECTS WHERE TYPE=''S'''

SELECT
TABLE_NAME

FROM #TEMP

DROP TABLE #TEMP

-------------------------
R..

bobk544
Starting Member

6 Posts

Posted - 2009-07-27 : 11:35:54

Great thanks a bunch Rajdaksha! will give it a try! have a good one!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources