sp_msforeachtable problem. - 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)

sp_msforeachtable problem.

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-26 : 13:54:28

hi
i want to retrieve all columns of all tables by this code :

exec sp_msforeachtable 'exec sp_columns ''?'''

but when i execute this script, sql server return number of empty result set, how to solve this problem ?
thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-26 : 14:24:10

you can get this information by running this too:-

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-26 : 15:35:26

Did you specify databasename while running?

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-26 : 17:09:24

i want use this code to get data from all database and tables for Sql 2000 and 2005
exec sp_msforeachtable 'exec sp_columns ''?'''

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-26 : 20:09:54

Not every system sp works in sp_msforeachtable.

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-27 : 12:28:57

quote:
Originally posted by rmiao

Not every system sp works in sp_msforeachtable.

now, how to solve my problem and retrieve all databases columns for sql 2000 and 2005 by my script or other suggestion ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-27 : 13:05:43

try this to get all column information inside a database

SELECT * FROM syscolumns
and try calling it inside sp_msforeachdb if you want column info from all dbs

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-27 : 13:13:05

Then it should be

Sp_Msforeachdb

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-27 : 13:59:20

thanks visakh16
but how to get dataType of each column and filter system columns ?
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-27 : 14:14:03

In 2005 INFORMATION_SCHEMA.COLUMN view returns the datatype of columns. In 2000 i think you have to make use of sp_MShelpcolumns stored procedure. syntax is sp_MShelpcolumns <tablename>

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 03:47:03

quote:
Originally posted by hdv212

thanks visakh16
but how to get dataType of each column and filter system columns ?
thanks

select * from informatio9n_schema.columns
where table_name='table_name'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 03:48:33

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101430

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources