question on executing a script for all user database

Question

Post reply

question on executing a script for all user database

k_bullen

Old Hand

Points: 383
More actions
November 16, 2010 at 8:59 am

#228892

Gotta question on running sp_msforeachdb.
To give a little background, we've had some horrible developers cycle through who had a bad habit of creating and populating huge tables only to never use them in production (and a former dba who never held anyone accountable, but that's a different story).
I'm trying to build a history up of the last time tables have been accessed to see what is being used and what isn't.
I have a script, and I want to execute it for each of the user databases on a server. I've tried using sp_msforeachdb, but the where clause in the selection from sys.dm_db_index_usage_stats fails on WHERE database_id = DB_ID() when I try to use [?] rather than DB_ID().
Is there a way to use this script with sp_msforeachdb, or is there a better way to do this? The temp table, #TableReadWriteHistory, is an example of the permanent table so you can run the code and see what the results are.
CREATE TABLE #TableReadWriteHistory
(
DatabaseName varchar(255) NOT NULL,
[Schema] varchar(255) NOT NULL,
Table_Or_View varchar(255) NOT NULL,
last_read datetime NULL,
last_write datetime NULL,
CONSTRAINT PK_TableReadWriteHistory_temp PRIMARY KEY CLUSTERED ( DatabaseName ASC, [Schema] ASC, Table_Or_View ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON )
)
CREATE TABLE #WT
(
DatabaseName VARCHAR(255) NULL,
[Schema] VARCHAR(255) NULL,
Table_Or_View VARCHAR(255) NULL,
last_read DATETIME NULL,
last_write DATETIME NULL
) ;
WITH agg
AS ( SELECT [object_id],
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
)
INSERT INTO #WT
(
[Schema],
Table_Or_View,
last_read,
last_write
)
SELECT [Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM ( SELECT [object_id],
last_user_seek,
NULL
FROM agg
UNION ALL
SELECT [object_id],
last_user_scan,
NULL
FROM agg
UNION ALL
SELECT [object_id],
last_user_lookup,
NULL
FROM agg
UNION ALL
SELECT [object_id],
NULL,
last_user_update
FROM agg
) AS x ( [object_id], last_read, last_write )
GROUP BY OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,
2 ;
DELETE FROM #WT
WHERE [Schema] IS NULL
OR [Schema] = 'sys' ;
UPDATE #WT
SET DatabaseName = DB_NAME() ;
INSERT INTO #TableReadWriteHistory
(
DatabaseName,
[Schema],
Table_Or_View,
last_read,
last_write
)
SELECT WT.[DatabaseName],
WT.[Schema],
WT.Table_Or_View,
WT.last_read,
WT.last_write
FROM #WT WT
LEFT OUTER JOIN #TableReadWriteHistory AS TRWH ON WT.[DatabaseName] = TRWH.DatabaseName
AND WT.[Schema] = TRWH.[Schema]
AND WT.Table_Or_View = TRWH.Table_Or_View
WHERE ( TRWH.DatabaseName IS NULL )
AND ( TRWH.[Schema] IS NULL )
AND ( TRWH.Table_Or_View IS NULL ) ;
UPDATE #TableReadWriteHistory
SET last_read = WT.last_read,
last_write = WT.last_read
FROM #WT WT
INNER JOIN #TableReadWriteHistory AS TRWH ON WT.DatabaseName = TRWH.DatabaseName
AND WT.[Schema] = TRWH.[Schema]
AND WT.Table_Or_View = TRWH.Table_Or_View
SELECT *
FROM #TableReadWriteHistory
DROP TABLE #TableReadWriteHistory
DROP TABLE #WT

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

this command seems to be working for me to return results on a per-db basis...is that what you wanted?

sp_msforeachdb

'select ''?'' as dbname,*

from sys.dm_db_index_usage_stats

where database_id = db_id(''?'')'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

k_bullen Old Hand Points: 383 More actions · Answer 2

Thanks, I was messed up trying to use square brackets rather than round and kept getting an error, but it works now. I think this will get me what I need.

Here's the whole proc, which is extremely ugly due to the 2000 character limit for the args of sp_msforeachdb:

assuming that the following table is in a database called DBA_Admin:

CREATE TABLE [dbo].[TableReadWriteHistory](

[DatabaseName] [varchar](255) NOT NULL,

[Schema] [varchar](255) NOT NULL,

[Table_Or_View] [varchar](255) NOT NULL,

[last_read] [datetime] NULL,

[last_write] [datetime] NULL,

CONSTRAINT [PK_TableReadWriteHistory] PRIMARY KEY CLUSTERED

(

[DatabaseName] ASC,

[Schema] ASC,

[Table_Or_View] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

EXEC dbo.sp_msforeachdb ' USE [?] ;

IF DB_ID(''?'') > 4 AND ''?'' <> ''DBA_Admin''

BEGIN CREATE TABLE #WT (DatabaseName VARCHAR(255) NULL,[Schema] VARCHAR(255) NULL,Table_Or_View VARCHAR(255) NULL,last_read DATETIME NULL,last_write DATETIME NULL); WITH agg AS (SELECT [object_id],[Schema]=OBJECT_SCHEMA_NAME([object_id]),last_user_seek,last_user_scan,last_user_lookup,last_user_update FROM sys.dm_db_index_usage_stats WHERE DB_NAME(database_id) = (''?'')) INSERT INTO #WT ([Schema],Table_Or_View,last_read,last_write) SELECT [Schema]=OBJECT_SCHEMA_NAME([object_id]),[Table_Or_View]=OBJECT_NAME([object_id]),last_read=MAX(last_read),last_write=MAX(last_write) FROM (SELECT [object_id],last_user_seek,NULL FROM agg UNION ALL SELECT [object_id],last_user_scan,NULL FROM agg UNION ALL SELECT [object_id],last_user_lookup,NULL FROM agg UNION ALL SELECT [object_id],NULL,last_user_update FROM agg) AS x ([object_id],last_read,last_write) GROUP BY OBJECT_SCHEMA_NAME([object_id]),OBJECT_NAME([object_id]) ORDER BY 1,2 ; DELETE FROM #WT WHERE [Schema] IS NULL OR [Schema]=''sys''; UPDATE #WT SET DatabaseName = DB_NAME(); INSERT INTO [DBA_Admin].[dbo].[TableReadWriteHistory] (DatabaseName,[Schema],Table_Or_View,last_read,last_write) SELECT WT.[DatabaseName],WT.[Schema],WT.Table_Or_View,WT.last_read,WT.last_write FROM #WT WT LEFT OUTER JOIN DBA_Admin.dbo.TableReadWriteHistory AS TRWH ON WT.[DatabaseName]=TRWH.DatabaseName AND WT.[Schema]=TRWH.[Schema] AND WT.Table_Or_View=TRWH.Table_Or_View WHERE (TRWH.DatabaseName IS NULL) AND (TRWH.[Schema] IS NULL) AND (TRWH.Table_Or_View IS NULL); UPDATE DBA_Admin.dbo.TableReadWriteHistory SET last_read=WT.last_read,last_write=WT.last_write FROM #WT WT INNER JOIN DBA_Admin.dbo.TableReadWriteHistory AS TRWH ON WT.DatabaseName=TRWH.DatabaseName AND WT.[Schema]=TRWH.[Schema] AND WT.Table_Or_View=TRWH.Table_Or_View DROP TABLE #WT END'

END