After recently completing a database development project I found the actual database tables in use had gotten out of step with my original (planned) Entity Relationship Model and its associated Data Schema. The reason for the differences in my data tables (from those which I had planned for) was due to the fact that when carrying out normalisation of the tables (in situ) in the database, duplicated tables (some containing additional fields) were included in order to accommodate additional information which had been stored in the original (Excel) data source. To check that all the tables in the database matched the original model (and also to target additional unused rows – for later removal) I used the following script. By executing the script in a query window in Microsoft SQL Server Management Studio (R2) a resulting Table and Column name listing was produced. This script was referenced on the ‘sqlauthority’ website listed below. However I made a slight modification (further sources are cited) in order to remove ‘auto created tables’ from the list in order to leave only tables that I had actually added to the database structure myself in the list. This allowed me to focus my attention on the tables which I had inserted from the outset (as distinct from system auto generated tables). I hope you find the following scripts useful. Whilst carrying out research for this work I stumbled on this excellent script source on the Simple Talk website:
This source provides additional scripts for delving into all manner of database and table statistic in T-SQL. Its a post I will no doubt be returning to in the future.The following script produces a listing of all Tables and Table Objects within a T-SQL database, including: TableName, ColumnName, SchemaName, TypeName, StatName, column_id and LastUpdated date. The sample shown is listing is for the familiar ‘Northwind’ database provided by Microsoft. Of course you’ll need to modify with your database name.
USE Northwind
GO
SELECT DISTINCT
-- OBJECT_NAME(s.[object_id]) AS TableName,
OBJECT_NAME(s.[OBJECT_ID]) TableName,
c.name AS ColumnName,
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TypeName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
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.columns AS c
FROM sys.stats s JOIN sys.columns sc ON sc.[object_id] = s.[object_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]
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
-- ###
-- Note, the following script was added in order to filter only auto created or user created tables
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1);