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 | https://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
Limitations:
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.
No comments