Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to show the statistics from all user tables from a DB Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 5:51 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:04 AM
Points: 21, Visits: 227
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

Post #1404717
Posted Wednesday, January 9, 2013 7:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 12,905, Visits: 32,181
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1404769
Posted Wednesday, January 9, 2013 8:34 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:04 AM
Points: 21, Visits: 227
Excelent!

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

thanks a lot!
Post #1404830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse