As a DBA, you probably have your toolbox full of scripts, procedures and functions that you use for the day-to-day administration of your instances.
I’m no exception and my hard drive is full of scripts that I tend to accumulate and never throw away, even if I know I will never need (or find?) them again.
However, my preferred way to organize and maintain my administration scripts is a database called “TOOLS”, which contains all the scripts I regularly use.
One of the challenges involved in keeping the scripts in a database rather than in a script file is the inability to choose a database context for the execution. When a statement is encapsulated in a view, function or stored procedure in a database, every reference to a database-specific object is limited to the database that contains the programmable object itself. The only way to overcome this limitation is the use of dynamic sql.
For instance, if I want to query the name of the tables in a database, I can use the following statement:
SELECT name FROM sys.tables
The statement references a catalog view specific to a single database, so if I enclose it in a stored procedure, the table names returned by this query are those found in the database that contains the stored procedure itself:
USE TOOLS; GO CREATE PROCEDURE getTableNames AS SELECT name FROM sys.tables; GO EXEC getTableNames;
This is usually not an issue, since most stored procedures will not cross the boundaries of the database they are created in. Administration scripts are different, because they are meant as a single entry point to maintain the whole SQL server instance.
In order to let the statement work against a different database, you need to choose one of the following solutions:
- dynamic SQL
- marking as a system object
- … an alternative way
Each of these techniques has its PROs and its CONs and I will try to describe them in this post.
1. Dynamic SQL
It’s probably the easiest way to solve the issue: you just have to concatenate the database name to the objects names.
USE TOOLS; GO ALTER PROCEDURE getTableNames @db_name sysname AS BEGIN DECLARE @sql nvarchar(max) SET @sql = 'SELECT name FROM '+ QUOTENAME(@db_name) +'.sys.tables'; EXEC(@sql) END GO EXEC getTableNames 'msdb';
- very easy to implement for simple statements
- can rapidly turn to a nightmare with big, complicated statements, as each object must be concatenated with the database name. Different objects have different ways to be related to the database: tables and views can be concatenated directly, while functions such as OBJECT_NAME accept an additional parameter to specify the database name.
- the statement has to be treated as a string and enclosed in quotes, which means that:
- quotes must be escaped, and escaped quotes must be escaped again and escaped and re-escaped quotes… ok, you know what I mean
- no development aids such as intellisense, just-in-time syntax checks and syntax coloring
It’s a neater way to avoid concatenating the database name to each object referenced in the statement.
USE TOOLS; GO ALTER PROCEDURE getTableNames @db_name sysname AS BEGIN -- use a @sql variable to store the whole query -- without concatenating the database name DECLARE @sql nvarchar(max); SET @sql = 'SELECT name FROM sys.tables'; -- concatenate the database name to the -- sp_executesql call, just once DECLARE @cmd nvarchar(max); SET @cmd = 'EXEC '+ QUOTENAME(@db_name) +'.sys.sp_executesql @sql'; EXEC sp_executesql @cmd, N'@sql nvarchar(max)', @sql END GO EXEC getTableNames 'msdb';
- the dynamic sql is taken as a whole and does not need to be cluttered with multiple concatenations
- needs some more work than a straight concatenation and can be seen as “obscure”
- suffers from the same issues found with plain dynamic sql, because the statement is, again, treated as a string
3. System object
Nice and easy: every stored procedure you create in the master database with the “sp_” prefix can be executed from any database context.
Using the undocumented stored procedure sp_MS_marksystemobject you can also mark the stored procedure as a “system object” and let it reference the tables in the database from which it is invoked.
USE master; GO ALTER PROCEDURE sp_getTableNames AS BEGIN SELECT name FROM sys.tables END GO EXEC sys.sp_MS_marksystemobject 'sp_getTableNames' GO USE msdb; GO EXEC sp_getTableNames;
- no need to use dynamic sql
- requires creating objects in the “master” database, which is something I tend to avoid
- works with stored procedures only (actually, it works with other objects, such as tables and views, but you have to use the “sp_” prefix. The day I will find a view named “sp_getTableNames” in the master database it won’t be safe to stay near me)
An alternative method:
It would be really helpful if we could store the statement we want to execute inside an object that doesn’t involve dynamic sql and doesn’t need to be stored in the master database. In other words, we need a way to get the best of both worlds.
Is there such a solution? Apparently, there isn’t.
The ideal object to store a statement and reuse it later is a view, but there is no way to “execute” a view against a different database. In fact you don’t execute a view, you just select from it, which is quite a different thing.
What you “execute” when you select from a view is the statement in its definition (not really, but let me simplify).
So, what we would need to do is just read the definition from a view and use the statement against the target database. Sounds straightforward, but it’s not.
The definition of a view also contains the “CREATE VIEW” statement and stripping it off is not just as easy as it seems.
Let’s see the issue with an example: I will create a view to query the last update date of the index statistics in a database, using the query from Glenn Berry’s Diagnostic Queries.
USE TOOLS; GO -- When were Statistics last updated on all indexes? (Query 48) CREATE VIEW statisticsLastUpdate AS SELECT DB_NAME() AS database_name ,o.NAME AS stat_name ,i.NAME AS [Index Name] ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date] ,s.auto_created ,s.no_recompute ,s.user_created ,st.row_count FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id] INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id] WHERE o.[type] = 'U';
I just had to remove ORDER BY and OPTION(RECOMPILE) because query hints cannot be used in views.
Querying the object definition returns the whole definition of the view, not only the SELECT statement:
SELECT OBJECT_DEFINITION(OBJECT_ID('statisticsLastUpdate')) AS definition
definition ------------------------------------------------------------------- -- When were Statistics last updated on all indexes? (Query 48) CREATE VIEW statisticsLastUpdate AS SELECT DB_NAME() AS database_name ,o.NAME AS stat_name ,i.NAME AS [Index Name] ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date] ,s.auto_created ,s.no_recompute (1 row(s) affected)
In order to extract the SELECT statement, we would need something able to parse (properly!) the view definition and we all know how complex it can be.
Fortunately, SQL Server ships with an undocumented function used in replication that can help solving the problem: its name is fn_replgetparsedddlcmd.
This function accepts some parameters, lightly documented in the code: fn_replgetparsedddlcmd (@ddlcmd, @FirstToken, @objectType, @dbname, @owner, @objname, @targetobject)
Going back to the example, we can use this function to extract the SELECT statement from the view definition:
SELECT master.sys.fn_replgetparsedddlcmd( OBJECT_DEFINITION(OBJECT_ID('statisticsLastUpdate')) ,'CREATE' ,'VIEW' ,DB_NAME() ,'dbo' ,'statisticsLastUpdate' ,NULL ) AS statement
statement --------------------------------------------------------------------- AS SELECT DB_NAME() AS database_name ,o.NAME AS stat_name ,i.NAME AS [Index Name] ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date] ,s.auto_created ,s.no_recompute ,s.user_created ,st.row_count (1 row(s) affected)
The text returned by the function still contains the “AS” keyword, but removing it is a no-brainer:
DECLARE @cmd nvarchar(max) SELECT @cmd = master.sys.fn_replgetparsedddlcmd( OBJECT_DEFINITION(OBJECT_ID('statisticsLastUpdate')) ,'CREATE' ,'VIEW' ,DB_NAME() ,'dbo' ,'statisticsLastUpdate' ,NULL ) SELECT @cmd = RIGHT(@cmd, LEN(@cmd) - 2) -- Removes "AS" SELECT @cmd AS statement
statement ------------------------------------------------------------------- SELECT DB_NAME() AS database_name ,o.NAME AS stat_name ,i.NAME AS [Index Name] ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date] ,s.auto_created ,s.no_recompute ,s.user_created ,st.row_count (1 row(s) affected)
Now that we are able to read the SELECT statement from a view’s definition, we can execute that statement against any database we like, or even against all the databases in the instance.
-- ============================================= -- Author: Gianluca Sartori - spaghettidba -- Create date: 2013-04-16 -- Description: Extracts the view definition -- and runs the statement in the -- database specified by @db_name -- If the target database is a pattern, -- the statement gets executed against -- all databases matching the pattern. -- ============================================= CREATE PROCEDURE [dba_execute_view] @view_name sysname ,@db_name sysname AS BEGIN SET NOCOUNT, XACT_ABORT, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; DECLARE @cmd nvarchar(max) DECLARE @sql nvarchar(max) DECLARE @vw_schema sysname DECLARE @vw_name sysname IF OBJECT_ID(@view_name) IS NULL BEGIN RAISERROR('No suitable object found for name %s',16,1,@view_name) RETURN END IF DB_ID(@db_name) IS NULL AND @db_name NOT IN ('[USER]','[SYSTEM]') AND @db_name IS NOT NULL BEGIN RAISERROR('No suitable database found for name %s',16,1,@view_name) RETURN END SELECT @vw_schema = OBJECT_SCHEMA_NAME(OBJECT_ID(@view_name)), @vw_name = OBJECT_NAME(OBJECT_ID(@view_name)) SELECT @cmd = master.sys.fn_replgetparsedddlcmd( OBJECT_DEFINITION(OBJECT_ID(@view_name)) ,'CREATE' ,'VIEW' ,DB_NAME() ,@vw_schema ,@vw_name ,NULL ) SELECT @cmd = RIGHT(@cmd, LEN(@cmd) - 2) -- Removes "AS" -- CREATE A TARGET TEMP TABLE SET @sql = N' SELECT TOP(0) * INTO #results FROM ' + @view_name + '; INSERT #results EXEC [dba_ForEachDB] @statement = @cmd, @name_pattern = @db_name; SELECT * FROM #results;' EXEC sp_executesql @sql ,N'@cmd nvarchar(max), @db_name sysname' ,@cmd ,@db_name END
The procedure depends on dba_ForEachDB, the stored procedure I posted a couple of years ago that replaces the one shipped by Microsoft. If you still prefer their version, you’re free to modify the code as you wish.
Now that we have a stored procedure that “executes” a view, we can use it to query statistics update information from a different database:
EXEC [dba_execute_view] 'statisticsLastUpdate', 'msdb'
We could also query the same information from all user databases:
EXEC [dba_execute_view] 'statisticsLastUpdate', '[USER]'
That’s it, very easy and straightforward.
Just one suggestion for the SELECT statements in the views: add a DB_NAME() column, in order to understand where the data comes from, or it’s going to be a total mess.
This is just the basic idea, the code can be improved in many ways.
For instance, we could add a parameter to decide whether the results must be piped to a temporary table or not. As you probably know, INSERT…EXEC cannot be nested, so you might want to pipe the results to a table in a different way.
Another thing you might want to add is the ability to order the results according to an additional parameter.
To sum it up, with a little help from Microsoft, we can now safely create a database packed with all our administration stuff and execute the queries against any database in our instance.
If you are one among the many that downloaded my consistency check stored procedure called “dba_RunCHECKDB”, you may have noticed a “small” glitch… it doesn’t work on SQL Server 2012!
This is due to the resultset definition of DBCC CHECKDB, which has changed again in SQL Server 2012. Trying to pipe the results of that command in the table definition for SQL Server 2008 produces a column mismatch and it obviously fails.
Fixing the code is very easy indeed, but I could never find the time to post the corrected version until today.
Also, I had to discover the new table definition for DBCC CHECKDB, and it was not just as easy as it used to be in SQL Server 2008. In fact, a couple of days ago I posted a way to discover the new resultset definition working around the cumbersome metadata discovery feature introduced in SQL Server 2012.
Basically, the new output of DBCC CHECKDB now includes 6 new columns:
CREATE TABLE ##DBCC_OUTPUT( Error int NULL, [Level] int NULL, State int NULL, MessageText nvarchar(2048) NULL, RepairLevel nvarchar(22) NULL, Status int NULL, DbId int NULL, -- was smallint in SQL2005 DbFragId int NULL, -- new in SQL2012 ObjectId int NULL, IndexId int NULL, PartitionId bigint NULL, AllocUnitId bigint NULL, RidDbId smallint NULL, -- new in SQL2012 RidPruId smallint NULL, -- new in SQL2012 [File] smallint NULL, Page int NULL, Slot int NULL, RefDbId smallint NULL, -- new in SQL2012 RefPruId smallint NULL, -- new in SQL2012 RefFile smallint NULL, -- new in SQL2012 RefPage int NULL, RefSlot int NULL, Allocation smallint NULL )
If you Google the name of one of these new columns, you will probably find a lot of blog posts (no official documentation, unfortunately) that describes the new output of DBCC CHECKDB, but none of them is strictly correct: all of them indicate the smallint columns as int.
Not a big deal, actually, but still incorrect.
As usual, suggestions and comments are welcome.
Do you use code templates in SSMS? I am sure that at least once you happened to click “New stored procedure” in the object explorer context menu.
The default template for this action is a bit disappointing and the only valuable line is “SET NOCOUNT ON”. The rest of the code has to be heavily rewritten or deleted. Even if you use the handy keyboard shortcut for “Specify values for template parameters” (CTRL+SHIFT+M), you end up entering a lot of useless values. For instance, I find it very annoying having to enter stored procedure parameters definitions separately for name, type and default value.
Moreover, one of the questions I see asked over and over in the forums at SqlServerCentral is how to handle transactions and errors in a stored procedure, something that the default template does not.
Long story short, I’m not very happy with the built-in template, so I decided to code my own:
-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <ProcedureName, sysname, > AS BEGIN SET NOCOUNT ON; SET XACT_ABORT, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; DECLARE @localTran bit IF @@TRANCOUNT = 0 BEGIN SET @localTran = 1 BEGIN TRANSACTION LocalTran END BEGIN TRY --Insert code here IF @localTran = 1 AND XACT_STATE() = 1 COMMIT TRAN LocalTran END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000) DECLARE @ErrorSeverity INT DECLARE @ErrorState INT SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() IF @localTran = 1 AND XACT_STATE() <> 0 ROLLBACK TRAN RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH END
This template can be saved in the default path and overwrite the kludgy “New Stored Procedure” built-in template.
Some things to keep in mind:
- I don’t use nested transactions (they’re totally pointless IMHO) and I check for an existing transaction instead.
- The stored procedure will commit/rollback the transaction only if it was started inside the procedure.
- I want every stored procedure to throw the errors it catches. If there’s another calling procedure, it will take care of the errors in the same way.
- This is your computer: you can safely replace <Author, ,Name> with your real name.
- It would really be nice if there was some kind of way to make SSMS fill <Create Date, ,> with the current date. Unfortunately there’s no way. If you are using CVS or some other kind of version control system, this is a nice place for an RCS string such as $Date$
- If you like templates parameters and you heard bad news regarding this feature in the next version of SQL Server (codename Denali), don’t worry: MS fixed it.