Tuesday, February 5, 2013

How to get Database Table Information in SQL Server

Running the below query on a sql server database will out put specific details about the each table in the database.

SELECT DISTINCT OBJECT_NAME(s.[object_id]) AS TableName,c.name AS ColumnName,s.name AS StatName,
s.[object_id],s.stats_id,sc.stats_column_id,sc.column_id,STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id] JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1 AND (s.auto_created = 1 OR s.user_created = 1);
Below is a snapshot of the output:

No comments:
Write comments
Recommended Posts × +