all columns info - SQL Server Forums

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

Transact-SQL (2005)

all columns info

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-07-14 : 07:44:13

Hi frnds..

i have 8 databases (4 system database,4 user created databases).

how can i get all the information about the columns available in all the 8 databases.

urgent.

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 07:45:33

use this query:-
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

and use sp_msforeachdb to run this in all db.

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-07-14 : 07:55:19

when i use information_schema.columns it showing for perticular db.

i need to see all the db columns information.

can't i see all the information from a perticular db.
how to use sp_msforeachdb

please help me

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 08:05:19

sp_msforeachdb 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS'

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-07-14 : 08:17:16

if i exec this sp i am getting a perticular db column info only 8 times.
not 8 databases.

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 13:46:11

Refer this
http://www.mssqltips.com/tip.asp?tip=1414

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 03:16:49

quote:
Originally posted by visakh16

sp_msforeachdb 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS'

That should be

sp_msforeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.COLUMNS'

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Failing to plan is Planning to fail

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-07-15 : 05:29:53

Thanks for your help friends....

one more when i execute this command i am getting scrolls between the each and every databases. can i remove those.

Thanks.

Thanks

Zakeer Sk

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 06:00:52

You need to apply the logic specified in the link I posted

Madhivanan

Failing to plan is Planning to fail

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-07-15 : 11:38:00

thank you Madhivanan.

can u plz advise me about the sql server 2005 DBA certification.

I want to do certification but i am not getting proper response.

Kindly me help me.

my email id is shaik.zakeer@gmail.com

Thanks & Best Regards
Zakeer Shaik

Thanks

Zakeer Sk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources