General discussion

Locked

Sp_help on all tables in a db at once?

By rvanderveer ·
We are trying to get information on a client's SQL Server that is exhibiting some performance problems. We are doing this remotely with pcAnywhere. We wanted to get the sp_help information but for all the tables at once, in other words in a batch orsp or something, so we wouldn't have to run it on hundreds of tables one at a time.
Anyone have an idea or a script they would like to share?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Sp_help on all tables in a db at once?

by MtnSqs In reply to Sp_help on all tables in ...

1. get a listing of all the tables.
2. Edit this into a script by adding the sp_help at front of every line

Collapse -

Sp_help on all tables in a db at once?

by rvanderveer In reply to Sp_help on all tables in ...

Poster rated this answer

Collapse -

Sp_help on all tables in a db at once?

by chrandrob In reply to Sp_help on all tables in ...

Create a cursor to query the sysobjects table to get the table names for the db you're in. Then iterate throught the tables, running sp_help for each one. This script will do that for you. Just change dbName to the name of the database.

Use dbName
Go

Declare @TbName sysname,
@SQL varchar(100)

Declare TbObjects Cursor fast_forward
For Select [name]
From sysobjects
Where type = 'U'
AND xtype = 'U'

Open TbObjects

Fetch Next From TbObjects Into @TbName

While @@Fetch_Status = 0
Begin
Set @SQL = "Exec sp_help " + @TbName
Exec(@SQL)

Fetch Next From TbObjects Into @TbName
End

Close TbObjects
Deallocate TbObjects

Collapse -

Sp_help on all tables in a db at once?

by rvanderveer In reply to Sp_help on all tables in ...

Poster rated this answer

Collapse -

Sp_help on all tables in a db at once?

by rvanderveer In reply to Sp_help on all tables in ...

This question was closed by the author

Back to Desktop Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums