As a DBA, it is common to get a request to run scripts against production databases. However, there can be environments where there are multiple databases on same instance, where the script needs to be run against. I have seen a environment where there were 50+ databases which have same schema with different data, and each database serving different customers.
When we get a request to run large scripts against many databases, at times with user over sight, it may be possible to miss running the script against one or more databases. The requirement comes to verify if the script was executed against all the databases. One way to verify if the script is executed against all databases is to pick an object (stored procedure, table, view, function) which was created as part of script execution, get the create date of that object and verify if it is showing the date and time when we ran the script. The challenge is to get the create date of a specific object from all databases at a time with little work.
Below is the code which will help in fetching the create date of the specified object (Stored Procedure, Table, View, Function) from all user databases on a instance. Pass the object name @ObjName in the 7th line of the code. Run the code and verify the create date from the output and make sure that the script was executed and created the object during the time the script was run.
-- Script Originally Written By: Keerthi Deep | http://www.SQLServerF1.com/ Set NOCOUNT ON Declare @ObjName nvarchar(300) declare @dbn nvarchar(200) Set @ObjName = 'Object_Name' -- Specify the name of the Stored Procedure/ Table/View/Function create table #DatabaseList(dbname nvarchar(2000)) Insert into #DatabaseList select name from sys.sysdatabases where name not in ('master', 'msdb', 'model','tempdb') order by name asc --select * from #DatabaseList Create table #Output_table (DB nvarchar(200), crdate datetime, ObjectName nvarchar(200)) declare c1 cursor for select dbname from #DatabaseList open c1 Fetch next from c1 into @dbn WHILE @@FETCH_STATUS = 0 BEGIN declare @Query nvarchar(2048) Set @Query = 'select ''' + @dbn + ''' as DBName, crdate, [name] from ' + @dbn + '.sys.sysobjects where name = ''' + @ObjName + '''' --print @Query Insert into #Output_table Exec sp_executesql @Query FETCH NEXT FROM c1 into @dbn END CLOSE c1 DEALLOCATE c1 select * from #Output_table Drop table #Output_table Drop table #DatabaseList
This will work only if the object is created using create command, but will not work if Alter command is used.
Any suggestions are welcome.
Interested in working with Keerthi? Schedule a tech call.