February 21, 2014 at 7:30 am
I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1
But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
SQL Padre
aka Robert M Bishop
"Do or do not, there is no try" -- Yoda
February 21, 2014 at 7:35 am
SQL_Padre (2/21/2014)
I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
You might take a quick peek at BOL.
http://technet.microsoft.com/en-us/library/ms177623.aspx
The very first column is object_id. "ID of the object to which these statistics belong."
Do you know how to find the table from that?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 21, 2014 at 8:06 am
as Sean alluded to , there's several ways to get the name; you could join to sys.objects, or use the built in functions like object_name:
SELECT OBJECT_SCHEMA_NAME(OBJECT_ID) AS SchemaName,
OBJECT_NAME(OBJECT_ID) AS ObjectName,
*
FROM sys.stats
WHERE user_created = 1
ORDER BY ObjectName
SELECT schemaz.name AS SchemaName,
objectz.name AS ObjectName,
statz.*
FROM sys.stats statz
inner join sys.objects objectz
on statz.object_id = objectz.object_id
inner join sys.schemas schemaz
on objectz.schema_id = schemaz.schema_id
WHERE statz.user_created = 1
ORDER BY ObjectName
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply