asked
Script that will give tablenames, rowcounts, top create date and top update date
I need some help to create a script that will give me all tables, rowcounts of those tables, top create date and top update date within those tables within a set database. I found a script that returns a rowcount, number of columns and size but still need dates and such from each table. Any help would be great.. Thanks USE Database GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update FROM sys.dm_db_index_usage_stats ius WHERE database_id = DB_ID( 'EmpGuide') AND last_user_update IS NOT NULL GROUP BY OBJECT_NAME(OBJECT_ID), last_user_update ORDER BY ius.last_user_update DESC
Well, as a starting point, you could replace your call to the sp_spaceused and so on with a query joining the sys.tables view with the sysindexes, and throwing in syscolumns for good measure: SELECT schema_name(t.schema_id), t.name, t.create_date, i.rowcnt, u.last_user_update, count(c.colid) AS Num_Columns FROM sys.tables t LEFT JOIN sysindexes i ON t.object_id =
i.id AND i.indid IN ( 0, 1 ) LEFT JOIN sys.dm_db_index_usage_stats u ON t.object_id = u.object_id AND u.index_id IN ( 0, 1 ) AND u.database_id = Db_id() LEFT JOIN syscolumns c ON t.object_Id =
c.id group by t.schema_id, t.name, t.create_date, i.rowcnt, u.last_user_update ORDER BY 1 Assuming you're on SQL2005 or above, that is. NB: create_date is the date at which the table was created. (EDIT: added dm\_db\_index\_usage\_stats and syscolumns stuff) (QUERY: does the sys.tables max\_column\_id\_used field always give the same as the count of entries in syscolumns? What if a column gets deleted? Do entries get shuffled back up the column list?) (EDIT: removed sys.tables max\_column\_id\_used field, as per Matt's comment...)
@ThomasRushton - re: your query - it doesn't. If a column gets deleted the other columns retain their `column_id` and `max_column_id_used` retains it's value.
@Matt - thanks for that. I've removed it from my answer. Now, what's still missing?
I think you will find it difficult to get the last create time, assuming you mean the last time a row was inserted. Bear in mind, also, that the stats in `index_usage_stats` won't persist through a system restart. SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id])), [row_count], [LastUpdateTime] FROM (SELECT [ddps].[object_id], SUM([row_count]) AS row_count, MAX([last_user_update]) AS LastUpdateTime FROM [sys].[dm_db_partition_stats] AS [ddps] LEFT OUTER JOIN sys.dm_db_index_usage_stats AS [ddius] ON [ddius].[object_id] = [ddps].[object_id] AND [ddius].[index_id] = [ddps].[index_id] GROUP BY [ddps].[object_id]) AS idat WHERE [object_id] IN (SELECT [o].[object_id] FROM [sys].[objects] AS [o] WHERE [o].[schema_id] = 1 OR [o].[schema_id] > 4) ORDER BY QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id]));
@Matt interesting... that query of yours has only returned items in the [rep] schema of the database I've just run it against...
@ThomasRushton - whoops. I forgot dbo was a default schema ;)
Within most of my tables we keep a created_date and a last_updated_date. So I originally was thinking I would have to run a While Loop to query each table for a MAX(Created_date) and MAX(last_updated_date) based off the original script posted. I didnt think sys.indexes would give me everything I needed. Does this seem like the long way to get to the same answer?
I would consider whether you really need it. Looking up the maximum value of a field for every table in the database could be really painful, depending on how big the tables are and how they are indexed.
created_date for the table? or for the record within the table? If it's for the table, then you could do away with it. If it's per record, then no, you'll need to carry on as you are. Sorry.
It is the created_date for the last record in the table so really I think I can do a TOP 1 if I ordered it by Created_Date DESC. We are trying to determine if teh table is constantly being used or if we have alot of dead wood that can be marked as 'readyfordeletion'
If that's what you're doing, then you might be better off just doing a SQL profile trace for 24 hours and analysing the results of that for references to the table. Or to SPs that reference the table.
Here's one I got a long time ago off of
SQLServerCentral.com and still use today. /********************************************************************** SCRIPT NAME: Rockmooses Table Sizes.sql PURPOSE: Gets table and index sizes. Change History: 07/05/2009 10:14 - gmilner: acquired from SQLServerCentral:
http://www.sqlservercentral.com/Forums/Topic119721-5-1.aspx **********************************************************************/ create table #table_size( name nvarchar(128), rows int, reserved_kb varchar(18), data_kb varchar(18), index_kb varchar(18), unused_kb varchar(18) ) insert #table_size exec sp_MsForeachTable 'exec sp_spaceused ''?''--,true' -- "uncomment" --,true above to run updateusage select top 10 name, rows, convert(int,replace(reserved_kb,' KB','')) as reserved_kb, convert(int,replace(data_kb,' KB','')) as data_kb, convert(int,replace(index_kb,' KB','')) as index_kb, convert(int,replace(unused_kb,' KB','')) as unused_kb from #table_size order by -- name asc rows desc drop table #table_size Thanks, Rockmoose!
