How to Retrieve User Statistics in SQL
If you're looking to analyze user statistics in a database, this SQL script can help you retrieve detailed information about the statistics of your database tables. Here's how you can do it:
The script queries the system tables to gather essential details, such as the name of the table, statistic name, column name, data type, and column length. This is useful for understanding the structure and size of your data in the database.
SQL Script:
SELECT
st.[Name] AS [TableName],
ss.[Name] AS StatisticName,
sc.[Name] AS [ColumnName],
t.[Name] AS DataType,
CASE
WHEN sc.max_length = -1 THEN 'varchar(max), nvarchar(max), varbinary(max) or xml'
ELSE CAST(sc.max_length AS VARCHAR(10))
END AS ColumnLength
FROM [sys].stats ss
JOIN [sys].tables st ON ss.OBJECT_ID = st.OBJECT_ID
JOIN [sys].stats_columns ssc ON ss.stats_id = ssc.stats_id AND st.OBJECT_ID = ssc.OBJECT_ID
JOIN [sys].columns sc ON ssc.column_id = sc.column_id AND st.OBJECT_ID = sc.OBJECT_ID
JOIN [sys].types t ON sc.system_type_id = t.system_type_id
WHERE ss.user_created = 1
ORDER BY t.[Name], st.[Name];
This query works by joining several system tables:
sys.stats: Contains statistics data.sys.tables: Provides information about the tables in the database.sys.stats_columns: Links statistics to their respective columns.sys.columns: Describes the columns in the tables.sys.types: Contains the data types of the columns.
The script is particularly helpful for analyzing custom user-created statistics in SQL Server, and it organizes the results in an ordered fashion based on the data type and table name.
