Discussion Locked

Sp_help on all tables in a db at once?

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

5 replies

by mtnsqs

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

by chrandrob

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

This discussion is closed to new replies.