/**** All Statistics for particular Column*******/
Use database_name
GO
SELECT S.NAME ,Object_name(S.OBJECT_ID) as TABLE_NAME
FROM SYS.OBJECTS AS O
INNER JOIN SYS.STATS AS S
ON O.OBJECT_ID = S.OBJECT_ID
INNER JOIN SYS.STATS_COLUMNS AS SC
ON SC.OBJECT_ID = S.OBJECT_ID
AND S.STATS_ID = SC.STATS_ID
WHERE (O.TYPE IN ('U'))
AND (INDEXPROPERTY(S.OBJECT_ID,S.NAME,'IsStatistics') = 1) /* only stats */
AND (COL_NAME(SC.OBJECT_ID,SC.COLUMN_ID) = 'Column_Name')
/***********************************************/
Example:
Create database A
GO
use A
GO
Create table a(a int , b int)
GO
insert into a values (1,1)
CREATE STATISTICS [stats_a_a] ON [dbo].[a]([a])
CREATE STATISTICS [stats_a_b] ON [dbo].[a]([b])
GO
-- All Statistics For particular column
SELECT S.NAME ,Object_name(S.OBJECT_ID) as TABLE_NAME
FROM SYS.OBJECTS AS O
INNER JOIN SYS.STATS AS S
ON O.OBJECT_ID = S.OBJECT_ID
INNER JOIN SYS.STATS_COLUMNS AS SC
ON SC.OBJECT_ID = S.OBJECT_ID
AND S.STATS_ID = SC.STATS_ID
WHERE (O.TYPE IN ('U'))
AND (INDEXPROPERTY(S.OBJECT_ID,S.NAME,'IsStatistics') = 1) /* only stats */
AND (COL_NAME(SC.OBJECT_ID,SC.COLUMN_ID) = 'b')
No comments:
Write commentsPlease do not enter spam links