Script to show the statistics from all user tables from a DB

Question

Post reply

Script to show the statistics from all user tables from a DB

Seed Vicious

SSC Veteran

Points: 297
More actions
January 9, 2013 at 5:51 am

#393908

Enjoy! 😀
Set nocount on
DECLARE crComando CURSOR READ_ONLY FOR
select name from sys.objects where type = 'u' order by name
DECLARE @name varchar(400)
OPEN crComando
FETCH NEXT FROM crComando INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE @message varchar(8000)
SELECT @message = 'SELECT '''+@name +''' as Objeto, STATS_DATE(OBJECT_ID,STATS_ID) StatDate,* FROM SYS.STATS WHERE OBJECT_ID = OBJECT_ID('''+@name +''')'
--PRINT @message
Exec(@message)
END
FETCH NEXT FROM crComando INTO @name
END
CLOSE crComando
DEALLOCATE crComando

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

nice effort!

just as a more streamlined form, you don't need a cursor to generate the same results:

you can do it as a single set based operation instead:

SELECT

object_schema_name(OBJECT_ID) as SchemaName,

object_name(OBJECT_ID) as Objeto,

STATS_DATE(OBJECT_ID,STATS_ID)

StatDate,*

FROM SYS.STATS

WHERE OBJECT_ID IN(SELECT object_id from sys.tables)

order by

object_schema_name(OBJECT_ID),

object_name(OBJECT_ID),

stats_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Seed Vicious SSC Veteran Points: 297 More actions · Answer 2

Excelent!

I was working in this for hours... and I can't resolve the problem... :hehe:

thanks a lot!