17 Administrative Transact-SQL
Thinking is the hardest work there is, which is probably the reason so few engage in it.
—Henry Ford
While database administration itself is beyond the scope of this book, we can still delve into administrative Transact-SQL in some depth. In many shops, the line between database programmer and database administrator is a gray one indeed, so, regardless of whether you’re a DBA, it’s handy to have a working knowledge of administrative Transact-SQL commands and syntax.
It’s common for shops to author custom stored procedures and Transact-SQL batches to perform administrative functions. Backups, index statistic refreshes, and data warehouse updates are examples of the types of tasks these routines usually perform. Using the SQL Server Agent service, they can be set up to run when system utilization is relatively low.
Oscar Wilde said, “Dullness is the coming of age of seriousness.” Oftentimes, database administration is the “coming of age” of database application development. Once an app is built, someone has to feed and care for it, including its database. This is usually a dreary task and a thankless job, so it makes sense to use scripts and stored procedures to automate it whenever possible.
There was a time when the first response of old-timers to SQL Server management issues was to fire up ISQL, the venerable predecessor of Query Analyzer. Since I started working with the product when it had little in the way of real administration tools, this has been my default, gut reaction for years. The tools that shipped with SQL Server (and its look-alike cousin, Sybase) were poor enough (remember the execrable SAF utility?) that there was little other choice. However, with SQL Server’s increasing complexity and the steady improvement of its graphical tools, this isn’t the necessity it once was. No matter how adept you are with Transact-SQL, your best bet for administrating SQL Server is to use the many nice graphical tools that come with it. Enterprise Manager, with its many built-in facilities and utilities, is a very capable administration tool. Gone are the days when the administrator was forced to resort to a mixed bag of Transact-SQL and third-party tools to get the job done. In fact, with all the functionality now present in the product, managing SQL Server using only Transact-SQL would be difficult if not impossible. Furthermore, Enterprise Manager has matured to the point that it has greatly diminished the need for third-party tools. The product offers a rich assortment of management facilities that’s coherent and easy to use. Before you go to the trouble of writing lots of elaborate custom procedures using Transact-SQL syntax that is at times rather obscure, check out what comes free in the box.
On that note, your first move in deciding what administrative tasks to automate and how to automate them is to peruse the system procedures that ship with SQL Server. There are a number of handy routines that come with SQL Server out of the box. They supplement Transact-SQL with useful functionality not found in the language itself, ranging from the simple listing of meta-data to specifying database option settings and configuring the server. The procedures included in Table 17.1 aren’t listed in order of importance, nor is the list anywhere near complete. They’re intended to spur your interest in the canned routines that come with the product so that you’ll get to know them for yourself.
In addition to the bevy of administration-related procedures that ship with SQL Server, there are a number of Transact-SQL commands that have to do with system and database administration. Table 17.2 lists some of the more pertinent ones.
As with the earlier list, this one is far from complete. It’s worth your time to scan the entirety of the Transact-SQL language for commands and syntax that can lessen your administrative workload.
There are also several Transact-SQL functions that relate to database administration. Technically, most of the functions in Transact-SQL play a role in server administration at one time or another since they end up in the stored procedures and scripts used to perform administrative tasks. Some of them you may be familiar with, some you might not be. Table 17.3 lists a few of the more important ones.
Automatic variables are predefined, read-only variables that have the prefix “@@.” The SQL Server Books Online usually refers to them as “functions,” but they aren’t really functions in the normal sense of the term (e.g., unlike functions, they can be passed as parameters to stored
procedures). Throughout this book, you’ll see them referred to as “automatic variables.” Because they’re global in scope—that is, they’re available from any database—many of them by nature relate to database or system administration. Table 17.4 lists the more prominent ones.
While getting familiar with administrative Transact-SQL syntax is certainly worthwhile, using it to build real-world procedures and scripts is far more interesting. Throughout the rest of the chapter, I’ll present a variety of stored procedures and scripts that utilize the Transact-SQL elements highlighted thus far for server and database administration. You can use these routines in your own work to provide functionality that’s missing or inconvenient to access in Enterprise Manager.
Status routines report on the status of the server, the users connected to it, the jobs running on it, etc. These types of routines are surprisingly valuable to the administrator. Database administrators like to know what’s going on. Keeping a finger on the pulse of the servers and databases under her care helps the DBA avoid unpleasant surprises like blocked processes, inaccessible objects, disgruntled users, and angry mobs. Here are a few of the more valuable status routines I’ve used over the years.
This routine tracks system activity over a period of time. Unlike sp_who, it benchmarks what’s going on at one point in time against what’s happening at another. It provides a number of useful statistics related to system CPU utilization, logical I/O, and physical I/O. It lets you see what type of work the server is doing at a particular time and who’s doing it. Here’s the code:
USE MASTER
GO
IF OBJECT_ID(’sp_active_processes’) IS NOT NULL
DROP PROC sp_active_processes
GO
CREATE PROC sp_active_processes
@loginame varchar(30)=NULL, -- ’ACTIVEONLY’ | spid | login name
@duration int=5 -- seconds to sample
/*
Object: sp_active_processes
Description: Shows system activity over a period of time
Usage: sp_active_processes [@loginame=login name | "ACTIVEONLY" | spid][,
@duration=seconds to monitor]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 4.2
Example usage:
sp_active_processes @duration=10 -- Monitors all processes for 10 seconds
sp_active_processes "ACTIVEONLY",30 -- Monitors all processes for 30 seconds,
but only lists active ones
sp_active_processes 34,5 -- Monitors spid 34 for 5 seconds
Created: 1991-05-11. Last changed: 1999-07-02.
*/
AS
SET NOCOUNT ON
DECLARE @before datetime,
@after datetime,
@lowlogin sysname,
@highlogin sysname,
@spidlow int,
@spidhigh int
SELECT @lowlogin=’’,
@highlogin=REPLICATE(’z’,TYPEPROPERTY(’sysname’,’Precision’)),
@spidlow=0,
@spidhigh=32767
-- Crack @loginame
IF (@loginame<>’ACTIVEONLY’) AND (@loginame IS NOT NULL) BEGIN
SELECT @lowlogin=@loginame,
@highlogin=@loginame
IF SUSER_SID(@lowlogin) IS NULL BEGIN
IF @loginame LIKE "[0-9]%"
SELECT @spidlow=CAST(@loginame AS int),
@spidhigh=CAST(@loginame AS int),
@lowlogin=’’,
@highlogin=REPLICATE(’z’,TYPEPROPERTY(’sysname’,’Precision’))
ELSE BEGIN
PRINT ’Invalid loginame’
PRINT CHAR(13)
GOTO Help
END
END
END
-- Get locks
SELECT spid,’L1’=COUNT(*),’L2’=0 INTO #LCKS FROM master..syslocks WHERE spid BETWEEN
@spidlow AND @spidhigh GROUP BY spid
-- Save off time
SELECT @before=CURRENT_TIMESTAMP
-- Get processes
SELECT SPID,LOGINAME,C1=CPU,C2=0,I1=PHYSICAL_IO,I2=0,CM1=CMD,CM2=CAST(’ LOGGED OFF’ AS
CHAR(16)),S1=CAST(STATUS AS CHAR(16)),S2=SPACE(16),B2=0,dbid=0,HOSTNAME=SPACE(10)
INTO #PRCS FROM master..sysprocesses WHERE loginame BETWEEN @lowlogin AND @highlogin
AND spid BETWEEN @spidlow AND @spidhigh
-- Wait for duration specified
DECLARE @WAITFORSTR varchar(30)
SET @WAITFORSTR=’WAITFOR DELAY
"’+CONVERT(char(8),DATEADD(ss,@duration,’19000101’),108)+’"’
EXEC(@WAITFORSTR)
-- Get the locks again
INSERT #LCKS SELECT DISTINCT spid,0,COUNT(*) FROM master..syslocks WHERE spid BETWEEN
@spidlow AND @spidhigh GROUP BY spid
-- Save off the time again
SELECT @after=CURRENT_TIMESTAMP
-- Get the processes a second time
INSERT #PRCS SELECT spid,loginame,0,CPU,0,PHYSICAL_IO,’ ’,CMD,’
’,STATUS,BLOCKED,DBID,HOSTNAME FROM master..sysprocesses
WHERE loginame BETWEEN @lowlogin AND @highlogin AND spid BETWEEN @spidlow AND @spidhigh
-- Put an entry for every process in the locks work table
INSERT #LCKS SELECT DISTINCT spid,0,0 FROM #PRCS
-- Grab the blockers out of the process and lock work tables
SELECT SPID=B2,BLKING=STR(COUNT(*),4)
INTO #BLK
FROM #PRCS WHERE B2<>0 GROUP BY B2
INSERT #BLK
SELECT DISTINCT l.spid,STR(0,4) FROM #LCKS l LEFT OUTER JOIN #BLK b ON (l.spid<>b.spid)
WHERE b.spid IS NULL
-- Print report header
PRINT ’STATISTICS FOR ’+@@SERVERNAME+’ AS OF ’+CAST(CURRENT_TIMESTAMP AS varchar)
PRINT ’ACTIVITY OF ’+CASE WHEN @lowlogin=@highlogin THEN ’LOGIN ’+@loginame ELSE
UPPER(LEFT(ISNULL(@loginame,’ALL’),6))+’ LOGINS’ END+’ FOR THE PAST
’+CAST(DATEDIFF(SS,@before,@after) AS varchar)+’ SECOND(S)’
PRINT CHAR(13)
-- Print report body
SELECT ’ A’=CASE WHEN P.spid=@@spid THEN ’*’ ELSE ’ ’ END+
CASE WHEN (L.L2<>L.L1)
OR (P.C2<>P.C1)
OR (P.I2<>P.I1)
OR (P.CM1<>P.CM2)
OR (P.S1<>P.S2)
THEN ’A’
ELSE ’I’
END,
SPID=STR(P.spid, 5),
LOGIN=LEFT(P.loginame,20),
HOST=P.HOSTNAME,
--C1, C2, I1, I2, L1, L2, CM1, CM2, S1, S2,
LOG_IO=STR(P.C2,10),
’ +/-’=SUBSTRING(’- +’,SIGN(P.C2-P.C1)+2,1)+LTRIM(STR(P.C2 - P.C1,6)),
’%Chg’=STR(CASE WHEN P.C1<>0 THEN (1.0*(P.C2-P.C1)/P.C1) ELSE 0 END*100,6,1),
PHYS_IO=STR(P.I2,10),
’ +/-’=SUBSTRING(’- +’,SIGN(P.I2-P.I1)+2,1)+LTRIM(STR(P.I2 - P.I1,6)),
’%Chg’=STR(CASE WHEN P.I1<>0 THEN (1.0*(P.I2-P.I1)/P.I1) ELSE 0 END*100,6,1),
LCKS=STR(L.L2,5),
’ +/-’=SUBSTRING(’- +’,SIGN(L.L2-L.L1)+2,1)+LTRIM(STR(L.L2 - L.L1,6)),
’%Chg’=STR(CASE WHEN L.L1<>0 THEN (1.0*(L.L2-L.L1)/L.L1) ELSE 0 END*100,6,1),
BLK=STR(P.B2 ,4),
BLKCNT=B.BLKING,
COMMAND=P.CM2,
STATUS=LEFT(P.S2,10),
DB=DB_NAME(P.DBID)
FROM (SELECT spid,
loginame=MAX(loginame),
C1=SUM(C1),
C2=SUM(C2),
I1=SUM(I1),
I2=SUM(I2),
CM1=MAX(CM1),
CM2=MAX(CM2),
S1=MAX(S1),
S2=MAX(S2),
B2=MAX(B2),
dbid=MAX(DBID),
hostname=MAX(HOSTNAME)
FROM #PRCS
GROUP BY spid) P,
(SELECT spid,
L1=SUM(L1),
L2=SUM(L2)
FROM #LCKS
GROUP BY spid) L,
#BLK B
WHERE P.spid=L.spid
AND P.spid=B.spid
AND (@loginame<>’ACTIVEONLY’
OR @loginame IS NULL
OR L.L2<>L.L1
OR P.C2<>P.C1
OR P.I2<>P.I1
OR P.CM1<>P.CM2
OR P.S1<>P.S2)
-- Print report footer
PRINT CHAR(13)+’TOTAL PROCESSES: ’+CAST(@@ROWCOUNT AS varchar)+CHAR(13)+’(A -
ACTIVE, I - INACTIVE, * - THIS PROCESS.)’
-- Delete work tables
DROP TABLE #LCKS
DROP TABLE #PRCS
DROP TABLE #BLK
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_active_processes’, @desc=’Shows system activity
over a period of time’,
@parameters=’[@loginame=login name | "ACTIVEONLY" | spid][, @duration=seconds
to monitor]’,
@example=’
sp_active_processes @duration=10 -- Monitors all processes for 10 seconds
sp_active_processes "ACTIVEONLY",30 -- Monitors all processes for 30 seconds,
but only lists active ones
sp_active_processes 34,5 -- Monitors spid 34 for 5 seconds’,
@author=’Ken Henderson’,@email=’[email protected]’,
@version=’4’,@revision=’2’,
@datecreated=’19910511’,@datelastchanged=’19990702’
RETURN -1
GO
sp_active_processes
(Results abridged)
STATISTICS FOR KH AS OF Jul 5 1999 12:39AM
ACTIVITY OF ALL LOGINS FOR THE PAST 5 SECOND(S)
This routine simulates sp_who in many ways, but it’s vastly improved over it in that you get a feel for actual system activity rather than just a basic report listing who’s logged in.
The code itself exhibits a couple of noteworthy elements. First, note the use of derived tables to embed the final process and blocking queries. This cuts down on the number of steps within the query itself, simplifying and shortening the code somewhat. Also, note the use of the CHAR() function to format the report delivered by PRINT. You can use CHAR() to perform lots of menial formatting tasks. Here, we use it to embed a carriage return in the string we’re about to display in order to ensure that there’s a line break between it and the line just printed. Doing this saves an extra call to PRINT. Curiously, placing CHAR(13) at the end of a PRINT statement doesn’t have the same effect. PRINT seems insistent on including one—and only one—carriage return at the end of each string it displays.
Note
The stored procedures and scripts in this chapter rely on various ancillary procedures not listed here. For example, the stored procedure sp_usage is used by the procedures in this book to list usage help when ’/?’ is passed as the first argument or when invalid parameter values are specified. You can find these routines detailed in Chapter 15, “Stored Procedures and Triggers,” and you can find their source code on the CD accompanying this book. I’ve included the source code in multiple places on the CD in order to make it easy to find.
Sp_pss returns detailed information about running processes. It allows you to spy on your users to an extent by displaying detailed process information, including the input and output buffers, for each connection. It uses DBCC PSS() to access this info from the server’s internal process-tracking facilities.
You can optionally set the @buffersonly parameter to “YES” to limit the procedure’s report to the input and output buffers of each process. As you may have guessed, these buffers constitute the last SQL batch submitted and the last results returned, respectively, for each connection. When you specify this option, sp_pss uses DBCC INPUTBUFFER() and DBCC OUTPUTBUFFER() rather than DBCC PSS() in order to construct its report. Note that these functions offer a subset of what DBCC PSS() itself provides. DBCC PSS() returns the buffers for a connection via its psrvproc->m_pwchLangBuff and psrvproc->srvio.outbuff columns, along with lots of other useful information.
Here’s the source to sp_pss:
USE master
go
IF OBJECT_ID(’sp_pss’) IS NOT NULL
DROP PROC SP_PSS
go
CREATE PROC sp_pss
@spid varchar(10)=’%’,
@buffersonly varchar(3)=’NO’
/*
Object: sp_pss
Description: Lists detail info for running processes
Usage: sp_pss [@spid=process id to list] (Defaults to all
processes)[,@buffersonly=YES|NO] -
determines whether the report is limited to the input/output buffers for each process
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 4.2
Example usage: sp_pss 8
Created: 1991-01-28. Last changed: 1999-06-02.
*/
AS
SET NOCOUNT ON
IF (@spid=’/?’) OR NOT EXISTS(SELECT * FROM sysprocesses WHERE spid LIKE @spid) GOTO
Help
SET @buffersonly=UPPER(@buffersonly)
DECLARE @sp int, @lname sysname
DECLARE Processes CURSOR
FOR SELECT spid, loginame FROM master..sysprocesses
WHERE spid LIKE @spid
AND HostProcess IS NOT NULL
AND HostProcess <> ’’
OPEN Processes
DBCC TRACEON(3604)
FETCH Processes INTO @sp, @lname
WHILE (@@FETCH_STATUS=0) BEGIN
IF (@buffersonly=’NO’) BEGIN
PRINT CHAR(13)+’Retrieving PSS info for spid: ’+CAST(@sp AS varchar)+’ user:
’+@lname
DBCC PSS(0,@sp)
END ELSE BEGIN
PRINT CHAR(13)+’Retrieving the input buffer for spid: ’+CAST(@sp AS varchar)+’
user: ’+@lname
PRINT CHAR(13)
DBCC INPUTBUFFER(@sp)
PRINT CHAR(13)+’Retrieving the output buffer for spid: ’+CAST(@sp AS varchar)+’
user: ’+@lname
PRINT CHAR(13)
DBCC OUTPUTBUFFER(@sp)
END
FETCH Processes INTO @sp, @lname
END
DBCC TRACEOFF(3604)
CLOSE Processes
DEALLOCATE Processes
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_pss’,@desc=’Lists detail info for running processes’,
@parameters=’[@spid=process id to list] (Defaults to all
processes)[,@buffersonly=YES|NO] -
determines whether the report is limited to the input/output buffers for each process’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’4’, @revision=’2’,
@example=’pips 8
sp_pss @buffersonly="YES"’,
@datecreated=’19910128’, @datelastchanged=’19990602’
RETURN -1
GO
sp_pss 14
(Results abridged)
The routine itself isn’t very complex because all the real work is done by DBCC. It gets the job done by opening a cursor on sysprocesses, looping through the table, and calling the appropriate DBCC function for each process.
When one process blocks another from accessing an object, it’s often because yet another process is blocking it. This situation can produce a virtual chain of resource blocks that is difficult to trace. It creates a veritable “whodunit” for the DBA—a mystery that requires tracking down the prime offenders—the processes that block others but are not blocked themselves.
The best tool for the sleuth in this case isn’t a magnifying glass or meerschaum pipe—it’s a stored procedure that traces process blocks back to their originators. That’s what sp_find_ root_blocker does. Here’s the code:
USE master
GO
IF OBJECT_ID(’sp_find_root_blocker’) IS NOT NULL
DROP PROC sp_find_root_blocker
GO
CREATE PROCEDURE sp_find_root_blocker @help char(2)=NULL
/*
Object: sp_find_root_blocker
Description: Finds the root offender(s) in the chain(s) of blocked processes
Usage: sp_find_root_blocker
Returns: spid of the root blocking process (returns the last one if there are multiple)
Created by: Ken Henderson. Email: [email protected]
Version: 6.0
Example: sp_find_root_blocker
Created: 1992-11-03. Last changed: 1999-07-05.
*/
AS
IF (@help=’/?’) GOTO Help
IF EXISTS (SELECT * FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON
(p1.spid=p2.blocked)) BEGIN
DECLARE @spid int
SELECT @spid=p1.spid -- Get the _last_ prime offender
FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked)
WHERE p1.blocked=0
SELECT p1.spid,
p1.status,
loginame=LEFT(p1.loginame,20),
hostname=substring(p1.hostname,1,20),
blk=CONVERT(char(3),p1.blocked),
db=LEFT(db_name(p1.dbid),10),
p1.cmd,
p1.waittype
FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked)
WHERE p1.blocked=0
RETURN(@spid) -- Return the last root blocker
END ELSE BEGIN
PRINT ’No processes are currently blocking others.’
RETURN(0)
END
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_find_root_blocker’, @desc=’Finds the root offender(s) in
the chain(s) of blocked processes’,
@parameters=’’, @returns=’spid of the root blocking process (returns the last one if
there are multiple)’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’6’, @revision=’0’,
@datecreated=’19921103’, @datelastchanged=’19990705’,
@example=’sp_find_root_blocker’
RETURN -1
GO
sp_find_root_blocker
(Results abridged)
spid status loginame hostname blk db cmd
------ ---------- ------------------------------------- ------------------ ------ ------ ----------
7 runnable SLUK_CREWKHEN SLUK_CREW 0 pubs SELECT
This routine simply performs a self-join of sysprocesses with itself to locate those processes that block others but are not themselves blocked. It then returns a result set of the prime offenders. Note the assignment of the @spid return variable. Using a SELECT that returns more than one row to assign a local variable results in the variable receiving the last value returned. This means that @spid will return the last prime blocker if there is more than one of them.
SQL Server includes a stored procedure, sp_lock, which returns useful info regarding resource locks. Unfortunately, it’s not as useful as it might be due to its inexplicable terseness. For example, rather than returning the name of an object that’s locked, sp_lock reports its object ID. Rather than listing the database name of each locked object, it reports its database ID only. And the report is completely void of any reference to the user actually maintaining the lock—it lists only the spid of the locking process, which is meaningless without cross-referencing the sysprocesses system table.
The normal course of action for the DBA is to run sp_lock and then translate the IDs of interest into their corresponding names using the appropriate functions and table references. This is tedious and shouldn’t be necessary. Here’s a stored procedure that provides those details for you:
USE master
GO
IF OBJECT_ID(’sp_lock_verbose’) IS NOT NULL
DROP PROC sp_lock_verbose
GO
CREATE PROC sp_lock_verbose @spid1 varchar(10)=NULL, @spid2 varchar(10)=NULL
/*
Object: sp_lock_verbose
Description: A more verbose version of sp_lock
Usage: sp_lock_verbose [@spid1=first spid to check][,@spid2=second spid to check]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 4.2
Example usage: sp_lock_verbose 18,25 -- checks spid’s 18 and 25
Created: 1994-11-18. Last changed: 1999-06-01.
*/
AS
SET NOCOUNT ON
IF (@spid1=’/?’) GOTO Help
DECLARE @dbid varchar(20), @dbname sysname, @objname sysname, @objid int, @execstr
varchar(8000), @nexecstr nvarchar(4000)
CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname NULL,
indid int, type char(4), resource char(15), mode char(10), status char(6))
-- Get basic locking info from sp_lock
INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status)
EXEC sp_lock @spid1, @spid2
-- Loop through the work table and translate each object id into an object name
DECLARE DBs CURSOR FOR SELECT DISTINCT dbid=CAST(dbid AS varchar) FROM #locks
OPEN DBs
FETCH DBs INTO @dbid
WHILE (@@FETCH_STATUS=0) BEGIN
SET @dbname=DB_NAME(@dbid)
EXEC master..xp_sprintf @execstr OUTPUT, ’UPDATE #locks SET objectname=o.name FROM
%s..sysobjects o WHERE (#locks.type=’’TAB’’ OR #locks.type=’’PAG’’) AND dbid=%s AND
#locks.objid=o.id’, @dbname, @dbid
EXEC(@execstr)
EXEC master..xp_sprintf @execstr OUTPUT, ’UPDATE #locks SET objectname=i.name FROM
%s..sysindexes i WHERE (#locks.type=’’IDX’’ OR #locks.type=’’KEY’’) AND dbid=%s AND
#locks.objid=i.id AND #locks.indid=i.indid’, @dbname, @dbid
EXEC(@execstr)
EXEC master..xp_sprintf @execstr OUTPUT, ’UPDATE #locks SET objectname=f.name FROM
%s..sysfiles f WHERE #locks.type=’’FIL’’ AND dbid=%s AND #locks.objid=f.fileid’,
@dbname, @dbid
EXEC(@execstr)
FETCH DBs INTO @dbid
END
CLOSE DBs
DEALLOCATE DBs
-- Return the result set
SELECT login=LEFT(p.loginame,20), db=LEFT(DB_NAME(l.dbid),30), l.type, object=CASE WHEN
l.type=’DB’ THEN LEFT(DB_NAME(l.dbid),30) ELSE LEFT(objectname,30) END, l.resource,
l.mode, l.status, l.objid, l.indid, l.spid
FROM #locks l JOIN sysprocesses p ON (l.spid=p.spid)
ORDER BY 1,2,3,4,5,6,7
DROP TABLE #locks
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_lock_verbose’, @desc=’A more verbose version of sp_lock’,
@parameters=’[@spid1=first spid to check][,@spid2=second spid to check]’,
@author=’Ken Henderson’,@email=’[email protected]’,
@version=’4’,@revision=’2’,
@datecreated=’19941118’, @datelastchanged=’19990601’,
@example="sp_lock_verbose 18,25 -- checks spid’s 18 and 25"
RETURN -1
GO
sp_lock_verbose
(Results abridged)
The execution path for this query is fairly straightforward. First, it runs sp_lock and “traps” its output in a temporary table using INSERT...EXEC. Next, it iterates through the temporary table and updates the objectname column based on the type of lock. Last, it returns the lock report as a result set, translating any remaining IDs as necessary.
You may be wondering why we don’t just use the OBJECT_NAME() function rather than query the system tables directly to translate the object IDs returned by sp_lock. The reason this is necessary is that OBJECT_NAME() doesn’t work across databases. That is, if the current database focus is pubs and you pass OBJECT_NAME() an ID from Northwind, you’ll get a NULL result unless that ID also happens to be used in pubs. So, we have to find a way to take the database context into account when looking up the object name. One way to do this would be to prefix the SQL we issue via the EXEC() function with ’USE dbname;’ where dbname is the name of the database we want to change to. Syntactically, this works but doesn’t return the result we want. OBJECT_NAME() still returns NULL for object IDs outside the current database.
Also, since the INFORMATION_SCHEMA views do not include object identifiers, we can’t use them to avoid querying the system tables directly. We receive only an object ID from sp_lock, so we must cross-reference a table or view that itself includes object IDs, such as the sysobjects, sysindexes, and sysfiles tables.
Catalog procedures return meta-data about objects stored by the server. SQL Server ships with a number of these, but you may find that you need more information than they provide or that you need it in a different format. When possible, you should avoid querying system catalog tables directly and use either the INFORMATION_SCHEMA views or the catalog procedures instead. Referencing the system tables indirectly will prevent your code from breaking should their exact layout change in a future release of SQL Server.
SQL Server provides several methods of getting at the columns contained in a table or view, but none of them is particularly handy. Sp_help, for example, provides a wealth of information, but its formatting is far from ideal. First, it returns the various elements of table meta-data as separate result sets. A single table may generate half a dozen result sets. Second, it doesn’t support wildcards, so you’re forced to inspect each table separately. Last, it doesn’t bother to trim the columns it displays, so many of them require the maximum width of sysname (128 characters) in screen real estate to display. You have to pan several screens just to view a basic column specification.
The COLUMNS INFORMATION_SCHEMA view suffers from many of the same defects, as well as some of its own. The procedures below were written to address many of these shortcomings. Perhaps they have flaws of their own, but at least they are different flaws. Here’s sp_table:
USE master
GO
IF OBJECT_ID(’sp_table’) IS NOT NULL
DROP PROC sp_table
GO
CREATE PROC sp_table @objectname sysname = ’%’
/*
Object: sp_table
Description: Lists the columns in a table
Usage: sp_table [@objectname]=Name of table or view to list catalog info for (defaults to ’%’)
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 4.2
Example: sp_table "authors"
Created: 1994-02-04. Last changed: 1999-04-11.
*/
AS
SET NOCOUNT ON
This routine provides a simple listing of the columns in a view or table by querying syscolumns, sysobjects, and systypes and truncating the data it displays to a reasonable length. It’s formatted similarly to the Oracle DESC command and provides a quick view of what columns are exposed by the object.
The query doesn’t do anything that’s particularly fancy. It doesn’t decode user-defined data types and doesn’t bother to report ancillary information such as constraints, indexes, or triggers. Its primary goal is to provide a quick snapshot of a table or view schema. It accepts wildcards, so you can list multiple tables at once from a given database.
Stand-alone database objects include tables, views, stored procedures, triggers, default objects, rule objects, and user-defined data types. Out of the box, SQL Server lacks a procedure or view that can list all of them at once. There’s no easy way to get a listing of all the objects you’ve created in a database without resorting to custom system table queries. That’s why sp_dir was written. It provides a listing similar to the operating system DIR command and includes all the objects matching a mask you specify. Here’s the code:
USE master
GO
IF OBJECT_ID(’sp_dir’) IS NOT NULL
DROP PROC sp_dir
GO
CREATE PROCEDURE sp_dir @mask varchar(30) = ’%’,
@obtype varchar(2) = ’U’,
@orderby varchar(8000)=’/N’
/*
Object: sp_dir
Description: Lists object catalog information similar to the OS DIR command.
Usage: sp_dir [@mask=name mask][,@obtype=object type][,@orderby=order switch[ ASC|DESC]]
@mask = pattern of object names to list (supports SQL wildcards); defaults to all objects
@obtype = type of objects to list (supports SQL wildcards); default to user tables
The following object types are supported:
U = User tables
S = System tables
V = Views
P = Stored procedures
X = Extended procedures
RF = Replication filter stored procedures
TR = Triggers
D = Default objects
R = Rule objects
T = User-defined data types
@orderby = column on which to sort listing.
Can also include ASC or DESC to specify ascending/descending order.
The following orderings are supported:
/N = by Name
/R = by number of rows
/S = by total object size
/D = by date created
/A = by total size of data pages
/X = by total size of index pages
/U = by total size of unused pages
/L = by maximum row length
/O = by owner
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 7.0
Example usage:
Parameters can be specified positionally, like so:
sp_dir ’TRA%’,’U’,’/S’
or by name, like so:
sp_dir @mask=’TRA%’,@obtype=’U’,@orderby=’/S DESC’
You can also specify additional ordering columns with @orderby, like so:
sp_dir @mask=’TRA%’,@obtype=’U’,@orderby=’/S DESC, row_count, date_created DESC’
All parameters are optional. If no parameters are specified, the following
command is executed:
sp_dir ’%’,’U’,’/N’
Created: 1992-06-12. Last changed: 1999-07-02.
*/
AS
SET NOCOUNT ON
IF (@mask=’/?’) GOTO Help
SELECT @orderby=UPPER(@orderby)
DECLARE @execstr varchar(8000)
SET @execstr=
"SELECT -- Get regular objects
’ ’=’ ’,
name=LEFT(o.name,30),
o.type,
date_created=o.crdate,
row_count=ISNULL(rows,0),
row_len_in_bytes=
ISNULL((SELECT SUM(length) FROM syscolumns WHERE id=o.id AND o.type in (’U’,’S’)),0),
total_size_in_KB=
ISNULL((SELECT SUM(reserved) FROM sysindexes WHERE indid in (0, 1, 255) AND id =
o.id),0)*2,
data_space_in_KB=
ISNULL(((SELECT SUM(dpages) FROM sysindexes WHERE indid > 2 AND id = o.id)+
(SELECT ISNULL(SUM(used), 0) FROM sysindexes WHERE indid = 255 AND id = o.id)),0)*2,
index_space_in_KB=
ISNULL(((SELECT SUM(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = o.id) -
((SELECT SUM(dpages) FROM sysindexes WHERE indid > 2 AND id = o.id)+
(SELECT ISNULL(SUM(used), 0) FROM sysindexes WHERE indid = 255 AND id = o.id))),0)*2,
unused_space_in_KB=
ISNULL(((SELECT SUM(reserved) FROM sysindexes WHERE indid in (0,1,255) AND id = o.id) -
(SELECT SUM(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = o.id)),0)*2,
owner=USER_NAME(o.uid)
FROM sysobjects o,
sysindexes i
WHERE o.name like ’"+@mask+"’ AND o.type LIKE ’"+@obtype+"’AND o.id*=i.id
AND i.indid>=1
UNION ALL -- Get user-defined data types
SELECT ’ ’, LEFT(name,30), ’T’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, USER_NAME(uid)
FROM systypes
WHERE (usertype & 256)<>0
AND name LIKE ’"+@mask
+"’AND ’T’ LIKE ’"+@obtype
+"’ UNION ALL -- Get totals
SELECT
’*’,
’{TOTAL}’,
NULL,
NULL,
SUM(row_count),
NULL,
SUM(total_size_in_KB),
SUM(data_space_in_KB),
SUM(index_space_in_KB),
SUM(unused_space_in_KB),
NULL
FROM
(SELECT
row_count=ISNULL(rows,0),
total_size_in_KB=
ISNULL((SELECT SUM(reserved) FROM sysindexes WHERE indid in (0, 1, 255) AND id =
o.id),0)*2,
data_space_in_KB=
ISNULL(((SELECT SUM(dpages) FROM sysindexes
WHERE indid > 2 AND id=o.id)+(SELECT ISNULL(SUM(used), 0)
FROM sysindexes WHERE indid = 255 AND id = o.id)),0)*2,
index_space_in_KB=
ISNULL(((SELECT SUM(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = o.id) -
((SELECT SUM(dpages) FROM sysindexes WHERE indid > 2 AND id = o.id)+
(SELECT ISNULL(SUM(used), 0) FROM sysindexes WHERE indid = 255 AND id = o.id))),0)*2,
unused_space_in_KB=
ISNULL(((SELECT SUM(reserved) FROM sysindexes WHERE indid in (0, 1, 255) AND id =
o.id)-
(SELECT SUM(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = o.id)),0)*2
FROM sysobjects o,
sysindexes i
WHERE o.name like ’"+@mask+"’ AND o.type LIKE ’"+@obtype+"’ AND o.id*=i.id
AND i.indid>=1) O
ORDER BY ’ ’,"+ -- Ensure that totals sort last
CASE LEFT(@orderby,2)
WHEN ’/N’ THEN ’name’+SUBSTRING(@orderby,3,8000) -- Include ASC/DESC flag if there is
one
ELSE
CASE LEFT(@orderby,2)
WHEN ’/D’ THEN ’date_created’
WHEN ’/S’ THEN ’total_size_in_KB ’
WHEN ’/R’ THEN ’row_count’
WHEN ’/A’ THEN ’data_space_in_KB’
WHEN ’/X’ THEN ’index_space_in_KB’
WHEN ’/U’ THEN ’unused_space_in_KB’
WHEN ’/L’ THEN ’row_len_in_bytes’
WHEN ’/O’ THEN ’owner’
END+SUBSTRING(@orderby,3,8000)+’,name’ -- Include name as secondary sort to resolve
ties
END
EXEC(@execstr)
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_dir’,
@desc=’Lists object catalog information similar to the OS DIR command.’,
@parameters=’[@mask=name mask][,@obtype=object type][,@orderby=order switch[
ASC|DESC]]
@mask = pattern of object names to list (supports SQL wildcards); defaults to all objects
@obtype = type of objects to list (supports SQL wildcards); default to user tables
The following object types are supported:
U = User tables
S = System tables
V = Views
P = Stored procedures
X = Extended procedures
RF = Replication filter stored procedures
TR = Triggers
D = Default objects
R = Rule objects
T = User-defined data types
@orderby = column on which to sort listing.
Can also include ASC or DESC to specify ascending/descending order.
The following orderings are supported:
/N = by Name
/R = by number of rows
/S = by total object size
/D = by date created
/A = by total size of data pages
/X = by total size of index pages
/U = by total size of unused pages
/L = by maximum row length
/O = by owner’,
@example="
Parameters can be specified positionally, like so:
sp_dir ’TRA%’,’U’,’/S’
or by name, like so:
sp_dir @mask=’TRA%’,@obtype=’U’,@orderby=’/S DESC’
You can also specify additional ordering columns with @orderby, like so:
sp_dir @mask=’TRA%’,@obtype=’U’,@orderby=’/S DESC, row_count, date_created DESC’
All parameters are optional. If no parameters are specified, the following
command is executed:
sp_dir ’%’,’U’,’/N’",
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’7’, @revision=’0’,
@datecreated=’19920612’, @datelastchanged=’19990702’
RETURN -1
GO
sp_dir ’t%’
name type date_created row_count row_len_in_bytes
---- ---------------------- ---- ------------------------- --------- ----------------
temp_authors U 1999-06-17 23:33:19.120 23 151
testident U 1999-05-19 17:52:29.570 132 14
testtxt U 1999-05-28 16:43:08.683 0 16
tid T NULL NULL NULL
titleauthor U 1998-11-13 03:10:49.220 25 22
titleauthor2 U 1999-05-28 16:10:34.153 25 22
titles U 1998-11-13 03:10:48.970 18 334
* {TOTAL} NULL NULL 223 NULL
The routine returns a number of useful object meta-data elements. It can be qualified by object name and type and can be sequenced by any of the columns it returns (including combinations of them).
As you can see from the result set fragment, user-defined data types are returned along with other types of objects despite the fact that they reside in a different system table than those objects. Developers tend to think of user-defined data types as of equal stature with other types of objects, so the procedure treats them uniformly.
There are a couple of features of the code itself that are worth mentioning. First, note that the procedure doesn’t use looping or control-flow syntax to generate its report. A single, rather large SELECT statement generates the result set returned by the procedure. The statement uses UNION ALL to aggregate the objects from sysobjects, systypes, and the table-related totals from sysobjects. This is more for syntactical amusement than anything else—storing each of the UNION terms separately in a temporary table and then listing the table would work equally well and might well be more efficient since the totals query could reference the temporary table rather than sysobjects.
UNION ALL is used rather than UNION because it’s more efficient in situations where you aren’t worried about duplicates. UNION removes duplicates from its result set before returning it; UNION ALL doesn’t. Here, the object names that come from sysobjects are guaranteed to be unique by the system anyway, and we wouldn’t want to remove duplicates between the systypes and sysobjects tables, so we use UNION ALL because it’s faster.
Another noteworthy feature of the query is its use of a dummy column to sequence the result set. We want the totals row to be the last row of the report, but we also want to allow sorting of the other rows. Remember that you can’t count on the natural order of a table—if you want a specific order, you must specify it with an ORDER BY clause. This makes the problem a little more difficult than simply placing the rows in a temporary table in the order in which we want to list them. The solution used here at the left of the report makes use of a pseudocolumn that contains either blanks for nontotals or an asterisk for totals and then uses that column as the first term in the ORDER BY clause, regardless of the sort order selected by the user. Since the value for this column is the same for all nontotal rows, the real sorting of those rows is controlled by the other ordering columns we specify, not the pseudocolumn.
One final point worth mentioning is the flexibility the procedure provides in ordering the result set. Beyond the simple mnemonics that can be passed to @orderby to specify a sort order, the procedure allows the DESC and ASC keywords of the Transact-SQL ORDER BY clause to be specified as well. Other ordering columns can also be specified, so ’/D DESC, owner ASC’ could be specified to sort the report in descending order by date created, then in ascending order by owner. This is made possible by the fact that the procedure uses the EXEC() function to execute a query that it constructs dynamically at runtime. Any ORDER BY terms passed into the procedure are simply appended to the end of the query following the pseudocolumn reference.
As of SQL Server 7.0, Transact-SQL includes a collection of system functions that are useful for accessing system meta-data. Getting at this catalog information previously required spelunking around in the system tables and translating lots of arcane bitmaps and fossilized column values. That’s no longer the case—Transact-SQL’s meta-functions make accessing system-level info much easier than it once was. The OBJECTPROPERTY(), TYPEPROPERTY(), COLUMNPROPERTY(), INDEXPROPERTY(), and DATABASEPROPERTY() functions are particularly handy in this regard.
The stored procedure that follows uses these functions, with some help from the system tables, to interrogate the object meta-data stored in a database. This amounts to providing textual descriptions of an object’s defining characteristics by examining its catalog info. There’s a wealth of available data there if you know where to look.
Similarly to sp_dir, sp_object lists detail-level information for an object or objects. It lists regular objects as well as user-defined data types and uses the ...PROPERTY() functions to yield the pertinent details of each. Here’s the source code to sp_object:
USE master
GO
DROP PROC sp_object
GO
CREATE PROC sp_object @objectname sysname=’%’, @orderby varchar(8000)=’1,2,3,4,5,6’
/*
Object: sp_object
Description: Returns detailed object info
Usage: sp_object [@objectname=name or mask of object(s) to list][,@orderby=ORDER
BY clause for query]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 7.0
Example: sp_object ’authors’
Created: 1994-06-29. Last changed: 1999-07-01.
*/
AS
IF (@objectname=’/?’) GOTO Help
EXEC("
SELECT Object=LEFT(O.Object,30), O.Type, ’SubType’=
CAST(CASE O.Type
WHEN ’Constraint’ THEN
CASE WHEN OBJECTPROPERTY(id,’IsCheckCnst’)=1 THEN ’Check Constraint’
WHEN OBJECTPROPERTY(id,’IsForeignKey’)=1 THEN ’Foreign Key Constraint’
WHEN OBJECTPROPERTY(id,’IsPrimaryKey’)=1 THEN ’Primary Key Constraint’
WHEN OBJECTPROPERTY(id,’IsDefaultCnst’)=1 THEN ’Default Constraint’
WHEN OBJECTPROPERTY(id,’IsUniqueCnst’)=1 THEN ’Unique Constraint’
END
WHEN ’Table’ THEN
CASE WHEN OBJECTPROPERTY(id,’TableIsFake’)=1 THEN ’Virtual’
WHEN OBJECTPROPERTY(id,’IsSystemTable’)=1 THEN ’System’
WHEN OBJECTPROPERTY(id,’IsUserTable’)=1 THEN ’User’
END
WHEN ’Trigger’ THEN
(SELECT ISNULL(SUBSTRING(’Insert ’, OBJECTPROPERTY(id,’ExecIsInsertTrigger’),7),’’)+
ISNULL(SUBSTRING(’Delete ’, OBJECTPROPERTY(id,’ExecIsDeleteTrigger’),7),’’)+
ISNULL(SUBSTRING(’Update ’, OBJECTPROPERTY(id,’ExecIsUpdateTrigger’),7),’’)+
ISNULL(SUBSTRING(’(Disabled) ’, OBJECTPROPERTY(id,’ExecIsTriggerDisabled’),11),’’))
WHEN ’Stored Procedure’ THEN
CASE WHEN OBJECTPROPERTY(id,’IsExtendedProc’)=1 THEN ’Extended’
WHEN OBJECTPROPERTY(id,’IsReplProc’)=1 THEN ’Replication’
ELSE ’User’
END
WHEN ’View’ THEN
CASE WHEN OBJECTPROPERTY(id,’OwnerId’)=3 THEN ’ANSI SQL-92’
WHEN OBJECTPROPERTY(id, ’IsMSShipped’)=1 THEN ’System’
ELSE ’User’
END
WHEN ’User-defined Data Type’ THEN
(SELECT name+
CASE WHEN name in (’char’,’varchar’,’nchar’,’nvarchar’) THEN
’(’+CAST(TYPEPROPERTY(Object,’Precision’) AS varchar)+’)’
WHEN name in (’float’,’numeric’,’decimal’,’real’,’money’,’smallmoney’) THEN
’(’+CAST(TYPEPROPERTY(Object,’Precision’) AS varchar)+ ’,’+
CAST(ISNULL(TYPEPROPERTY(Object,’Scale’),0) AS varchar)+’)’
ELSE ’’
END
FROM systypes WHERE (type=id) AND (usertype & 256)=0 AND
(name<>’sysname’) AND
prec=(SELECT MAX(prec) FROM systypes WHERE type=id))
END
AS varchar(25)),
Owner=LEFT(USER_NAME(uid),25),
’System-Supplied’=
CASE Type
WHEN ’User-defined Data Type’ THEN ’NO’ -- Can’t be, by definition
ELSE
CASE OBJECTPROPERTY(id,’IsMSShipped’) WHEN 0 THEN ’NO’ ELSE ’YES’ END
END,
Description=
SUBSTRING(
CASE WHEN O.Type=’Constraint’ THEN
(SELECT ISNULL(SUBSTRING(’,Clustered Key,’, OBJECTPROPERTY(id,
’CnstIsClustKey’),30),’’)+
ISNULL(SUBSTRING(’,Column Constraint,’,OBJECTPROPERTY(id,’CnstIsColumn’),30),’’)+
ISNULL(SUBSTRING(’,Disabled,’,OBJECTPROPERTY(id,’CnstIsDisabled’),30),’’)+
ISNULL(SUBSTRING(’,Non-clustered key,’, OBJECTPROPERTY(id,
’CnstIsNonClustKey’),30),’’)+ISNULL(SUBSTRING(’,NOT FOR
REPLICATION,’,OBJECTPROPERTY(id,’CnstIsNotRepl’),30),’’))
WHEN O.Type=’Table’ THEN
(SELECT CASE
WHEN OBJECTPROPERTY(id,’TableHasDeleteTrigger’)=1 THEN
’,# DELETE trig.:’+CAST(OBJECTPROPERTY(id,
’TableDeleteTriggerCount’) AS varchar) ELSE ’’ END+
CASE WHEN OBJECTPROPERTY(id,’TableHasInsertTrigger’)=1 THEN
’,# INSERT trig.:’+ CAST(OBJECTPROPERTY(id,
’TableInsertTriggerCount’) AS varchar) ELSE ’’ END+
CASE WHEN OBJECTPROPERTY(id,’TableHasUpdateTrigger’)=1 THEN
’,# UPDATE trig.:’+CAST(OBJECTPROPERTY(id,
’TableUpdateTriggerCount’) AS varchar) ELSE ’’ END+
’,Full-text index?:’+RTRIM(SUBSTRING(’NO YES’, (OBJECTPROPERTY(id,
’TableHasActiveFulltextIndex’)*3)+1,3))+
(CASE WHEN OBJECTPROPERTY(id, ’TableHasActiveFullTextIndex’)=1
THEN ’,Full-text catalog ID: ’+ISNULL(CAST(OBJECTPROPERTY(id,
’FulltextCatalogID’) AS varchar),’(None)’)+
’,Full-text key column: ’+
ISNULL((SELECT name FROM syscolumns WHERE id=id and
colid=OBJECTPROPERTY(id,’TableFulltextKeyColumn’)),’(None)’)
ELSE ’’ END)+
’,Primary key?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasPrimaryKey’)*3)+1,3))+
’,Check cnst?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasCheckCnst’)*3)+1,3))+
’,Default cnst?:’+RTRIM(SUBSTRING(’NO YES’, (OBJECTPROPERTY(id,
’TableHasDefaultCnst’)*3)+1,3))+
’,Foreign key?:’+RTRIM(SUBSTRING(’NO YES’,( OBJECTPROPERTY(id,
’TableHasForeignKey’)*3)+1,3))+
’,Foreign key ref?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasForeignRef’)*3)+1,3))+
’,Unique cnst?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasUniqueCnst’)*3)+1,3))+
’,Indexed?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasIndex’)*3)+1,3))+
’,Clust. idx?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasClustIndex’)*3)+1,3))+
’,Non-clust. idx?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasNonclustIndex’)*3)+1,3))+
’,Identity?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasIdentity’)*3)+1,3))+
’,ROWGUIDCOL?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasRowGUIDCol’)*3)+1,3))+
’,Text col.?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasTextImage’)*3)+1,3))+
’,Timestamp?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableHasTimestamp’)*3)+1,3))+
’,Pinned?:’+RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,
’TableIsPinned’)*3)+1,3)))
WHEN O.Type=’User-defined Data Type’ THEN
(SELECT ’,Allows NULLs?:’+RTRIM(SUBSTRING(’NO YES’,
(TYPEPROPERTY(Object,’AllowsNull’)*3)+1,3))+
ISNULL(’,Uses ANSI trim?:’+RTRIM(SUBSTRING(’NO YES’,
(TYPEPROPERTY(Object,’UsesANSITrim’)*3)+1,3)),’’))
WHEN O.Type IN (’Trigger’,’Stored Procedure’,’View’) THEN
(SELECT ’,ANSI NULLS=’+RTRIM(SUBSTRING(’OFFON ’,
(OBJECTPROPERTY(id,’ExecIsAnsiNullsOn’)*3)+1,3))+
’,Startup=’+RTRIM(SUBSTRING(’FALSETRUE ’,
(OBJECTPROPERTY(id,’ExecIsStartUp’)*5)+1,5))+
’,QuotedIdent=’+RTRIM(SUBSTRING(’FALSETRUE ’,(OBJECTPROPERTY(id,
’ExecIsQuotedIdentOn’)*5)+1,5)))
END
,2,4000)
FROM (
SELECT Object=name,
’Type’=
CASE
WHEN OBJECTPROPERTY(id,’IsConstraint’)=1 THEN ’Constraint’
WHEN OBJECTPROPERTY(id,’IsDefault’)=1 THEN ’Default Object’
WHEN OBJECTPROPERTY(id,’IsProcedure’)=1 OR
OBJECTPROPERTY(id,’IsExtendedProc’)=1 OR
OBJECTPROPERTY(id,’IsReplProc’)=1 THEN ’Stored Procedure’
WHEN OBJECTPROPERTY(id,’IsRule’)=1 THEN ’Rule Object’
WHEN OBJECTPROPERTY(id,’IsTable’)=1 THEN ’Table’
WHEN OBJECTPROPERTY(id,’IsTrigger’)=1 THEN ’Trigger’
WHEN OBJECTPROPERTY(id,’IsView’)=1 THEN ’View’
ELSE ’Unknown’
END,
id,
uid
FROM sysobjects
WHERE name LIKE ’"+@objectname+"’
UNION ALL
SELECT name, ’User-defined Data Type’,
type,
uid
FROM systypes
WHERE (usertype & 256)<>0
AND name LIKE ’"+@objectname+"’
) O
ORDER BY "+@orderby
)
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_object’, @desc=’Returns detailed object info’,
@parameters=’[@objectname=name or mask of object(s) to list][,@orderby=ORDER BY clause
for query]’,
@author=’Ken Henderson’,@email=’[email protected]’,
@version=’7’,@revision=’0’,
@datecreated=’19940629’,@datelastchanged=’19990701’,
@example=’sp_object ’’authors’’ ’
RETURN -1
GO
sp_object ’authors’
(Results abridged)
Object Type SubType Owner System-Supplied Description
------------ ------- ------------ --------- ------------------------ --------------------------------
authors Table User dbo NO Full-text index?:NO,Primary
key?:YES,Check cn
sp_object ’ti%’
(Results abridged)
This procedure uses Transact-SQL’s meta-data functions to probe the object-level information stored in the system catalogs. It accepts wildcards and lists each object’s name, type, subtype, owner, and origin, along with a free-form description field further depicting the object’s makeup.
As with sp_dir, sp_object uses UNION ALL to combine the objects found in the sysobjects and systypes tables. Since it doesn’t generate report totals, sp_dir’s second UNION—used solely to compute totals—isn’t needed. Also like sp_dir, this routine constructs at runtime a query that it then executes. This allows the ORDER BY criteria to be specified directly by the user.
Note the use of a derived table to simplify the query. The derived table allows us to use the values it yields to qualify the outer query. That is, rather than coding CASE OBJECTPROPERTY(id, ’IsTable’) in the outer query, we can code CASE Type WHEN ’Table’ ... instead. This is much more readable and helps modularize the code to an extent.
Note the use of the expression:
RTRIM(SUBSTRING(’NO YES’,(OBJECTPROPERTY(id,’TableHasPrimaryKey’)*3)+1,3))
to translate the 1 or 0 returned by OBJECTPROPERTY() into “YES” or “NO.” This is functionally equivalent to:
CASE OBJECTPROPERTY(’TableHasPrimaryKey’) WHEN 0 THEN ’No’ ELSE ’Yes’ END
I used this technique because there are already dozens of examples of CASE in the procedure and it’s good to be aware of the other options available to you for translating integers into string tokens. Also, the sheer number of CASE expressions in the query can be a bit overwhelming at first—breaking it up with deviations like this helps alleviate some of the monotony without really affecting performance.
The code used to decode user-defined data types is also of interest. The subquery:
(SELECT name+
CASE WHEN name in (’char’,’varchar’,’nchar’,’nvarchar’) THEN
’(’+CAST(TYPEPROPERTY(Object,’Precision’) AS varchar)+’)’
WHEN name in (’float’,’numeric’,’decimal’,’real’,’money’,’smallmoney’) THEN
’(’+CAST(TYPEPROPERTY(Object,’Precision’) AS varchar)+ ’,’+ CAST(ISNULL(TYPEPROPERTY(Object,’Scale’),0) AS varchar)+’)’
ELSE ’’
END
FROM systypes WHERE (type=id) AND (usertype & 256)=0 AND
(name<>’sysname’) AND
prec=(SELECT MAX(prec) FROM systypes WHERE type=id))
determines the underlying base type of a UDDT by scanning the systypes system table for the largest base type (usertype & 256 = 0) whose type field matches the id column exposed by the query. We scan for the largest type because our subquery is allowed to return only one value. It’s entirely possible that there’s more than one base type with the same type ID. For character data types, this will always be the case due to the inclusion of their Unicode versions. Returning only the largest types means that nchar and nvarchar will be ignored since they have less precision (4000 characters vs. 8000 characters) than their non-Unicode siblings.
Automating the maintenance of the system is probably the single most common use of administrative Transact-SQL. Most people, even DBAs, don’t like to spend their time manually maintaining and keeping their systems tuned. Few subscribe to Marguerite Duras’s assertion that “the best way to fill time is to waste it.” Most people have better things to do.
To that end, below you’ll find an assortment of maintenance procedures and scripts that I’ve used in my own work to make life easier as it relates to database and system administration. Many of these are the types of routines that you can schedule via the SQL Server Agent service. Most of them perform tasks that you’ll want to complete on a regular basis, so it’s sensible to schedule them to run automatically when possible.
Regardless of whether you allow SQL Server automatically to maintain the statistics that it uses to optimize queries, you may still need to update these statistics manually on an occasional basis. There are a couple of reasons for this. First, the server uses sampling techniques to minimize the time spent automatically generating statistics. Sometimes these samples aren’t representative of a table’s overall data and prevent the optimizer from properly optimizing queries. In that
case, you may have to help the server a bit by creating the statistics yourself using CREATE STATISTICS or UPDATE STATISTICS.
Another reason you may wish to update statistics manually is that you may be forced to disable automatic statistics generation for performance reasons. When autogeneration is enabled, the server ages and rebuilds statistics as needed when it optimizes queries that use those statistics. This process costs a certain amount of resources and processor time. In high-volume transactional environments, you may find that it’s more efficient to update statistics manually once a day or once a week than to allow them to be maintained automatically by the server. The trade-off here is similar to the one you face when deciding whether to drop nonclustered indexes before bulk data operations—you may find that it’s more efficient to “buy now and pay later” with index statistics than to “pay as you go.”
The following is a stored procedure that updates the statistics for all the tables in the database or databases you specify. It accepts wildcards and simply calls the SQL Server system procedure sp_updatestats to update the statistics in each database. Here’s the code:
USE master
IF OBJECT_ID(’sp_updatestats_all’) IS NOT NULL
DROP PROC sp_updatestats_all
GO
CREATE PROC sp_updatestats_all @dbname sysname=’%’
/*
Object: sp_updatestats_all
Description: Updates index statistics for a given database or databases
Usage: sp_updatestats_all [@dbname=Name of database to update (Default: "%")]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 4.2
Example: sp_updatestats_all "pubs"
Created: 1991-09-12. Last changed: 1999-05-03.
*/
AS
SET NOCOUNT ON
IF (@dbname=’/?’) GOTO Help
DECLARE Databases CURSOR FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE NOT
(CATALOG_NAME IN (’tempdb’,’master’,’msdb’,’model’)) -- Omit system DBs
AND CATALOG_NAME LIKE @dbname
DECLARE @execstr varchar(8000)
OPEN Databases
FETCH Databases INTO @dbname
IF (@@FETCH_STATUS<>0) BEGIN -- No matching databases
CLOSE Databases
DEALLOCATE Databases
PRINT ’No databases were found that match "’+@dbname+’"’
GOTO Help
END
WHILE (@@FETCH_STATUS=0) BEGIN
PRINT CHAR(13)+’Updating statistics information for database: ’+@dbname
-- Prefixing the DB name temporarily changes the current DB
SET @execstr=’EXEC ’+@dbname+’..sp_updatestats’
EXEC(@execstr)
FETCH Databases INTO @dbname
END
CLOSE Databases
DEALLOCATE Databases
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_updatestats_all’, @desc=’Updates index statistics
for a given database or databases’,
@parameters=’[@dbname=Name of database to update (Default: "%")]’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’4’, @revision=’2’,
@datecreated=’19910912’, @datelastchanged=’19990503’,
@example=’sp_updatestats_all "pubs"’
RETURN -1
sp_updatestats_all
Updating statistics information for database: Northwind
Updating dbo.employees
Updating dbo.categories
Updating dbo.customers
Updating dbo.dtproperties
Updating dbo.shippers
Updating dbo.suppliers
Updating dbo.orders
Updating dbo.products
Updating dbo.order details
Updating dbo.customercustomerdemo
Updating dbo.customerdemographics
Updating dbo.region
Updating dbo.territories
Updating dbo.employeeterritories
Statistics for all tables have been updated.
Updating statistics information for database: pubs
Updating dbo.authors
Updating dbo.publishers
Updating dbo.titles
Updating dbo.titleauthor
Updating dbo.stores
Updating dbo.sales
Updating dbo.roysched
Updating dbo.discounts
Updating dbo.jobs
Updating dbo.pub_info
Updating dbo.employee
Updating dbo.bets
Updating dbo.testident
Updating dbo.dtproperties
Updating dbo.titleauthor2
Updating dbo.authors2
Updating dbo.testtxt
Updating dbo.authors22
Updating dbo.temp_authors
Statistics for all tables have been updated.
This routine isn’t terribly complicated. It opens a cursor on the INFORMATION_SCHEMA SCHEMATA view and then iterates through the databases listed by the view, executing sp_ updatestats for each one. Note that the query could have queried sysdatabases instead, but using an INFORMATION_SCHEMA view is always preferable when one that meets your needs is available.
The actual call to sp_updatestats uses the trick, demonstrated earlier in the chapter and elsewhere in this book, of prefixing the system procedure name with the name of the database in order to change the database context temporarily:
EXEC dbname..sp_updatestats
This causes the procedure to run in the context of the database dbname, as though a USE dbname had immediately preceded the call to EXEC.
Like sp_updatestats_all, sp_updateusage_all iterates through the databases on the current server to update system-level information. Specifically, it executes DBCC UPDATEUSAGE() to correct errors in sysindexes that can cause inaccuracies in the object sizes listed by stored procedures such as sp_spaceused and sp_dir. Here’s the code:
USE master
IF OBJECT_ID(’sp_updateusage_all’) IS NOT NULL
DROP PROC sp_updateusage_all
GO
CREATE PROC sp_updateusage_all @dbname sysname=’%’
/*
Object: sp_updateusage_all
Description: Corrects usage errors in sysindexes
Usage: sp_updateusage_all [@dbname=Name of database to update (Default: "%")]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 4.2
Example: sp_updateusage_all "pubs"
Created: 1991-09-12. Last changed: 1999-05-03.
*/
AS
SET NOCOUNT ON
IF (@dbname=’/?’) GOTO Help
DECLARE Databases CURSOR FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
-- Omit system DBs
WHERE NOT (CATALOG_NAME IN (’tempdb’,’master’,’msdb’,’model’))
AND CATALOG_NAME LIKE @dbname
DECLARE @execstr varchar(8000)
OPEN Databases
FETCH Databases INTO @dbname
IF (@@FETCH_STATUS<>0) BEGIN -- No matching databases
CLOSE Databases
DEALLOCATE Databases
PRINT ’No databases were found that match "’+@dbname+’"’
GOTO Help
END
WHILE (@@FETCH_STATUS=0) BEGIN
PRINT CHAR(13)+
’Updating sysindexes usage information for database: ’+@dbname
SET @execstr=’DBCC UPDATEUSAGE(’+@dbname+’) WITH COUNT_ROWS, NO_INFOMSGS’
EXEC(@execstr)
FETCH Databases INTO @dbname
END
CLOSE Databases
DEALLOCATE Databases
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_updateusage_all’, @desc=’Corrects usage errors in
sysindexes’,
@parameters=’[@dbname=Name of database to update (Default: "%")]’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’4’, @revision=’2’,
@datecreated=’19910912’, @datelastchanged=’19990503’,
@example=’sp_updateusage_all "pubs"’
RETURN -1
If no errors are found in sysindexes, DBCC UPDATEUSAGE() returns no output, so you’ll often see nothing but the “Updating sysindexes information for database...” message that the procedure generates for each database. Also, DBCC UPDATEUSAGE() can take some time to run for large tables. You should issue sp_updateusage_all with care and preferably when system utilization is low.
There are times when you’ll need to rebuild all the indexes for a given table or tables. Bulk data loads, nightly posts, and other types of massive data updates are examples of operations that can necessitate index rebuilds. The procedure below uses DBCC DBREINDEX() to rebuild the indexes on all the tables in the databases you specify. Rebuilding indexes in this way allows the indexes that service PRIMARY KEY and UNIQUE constraints to be rebuilt without having to recreate those constraints manually. It also allows a table’s indexes to be rebuilt without knowing anything about the table.
Because DBCC DBREINDEX() can rebuild all the indexes on a table in a single statement, it is inherently atomic, which means that either all the index creations will occur or none of them will. Comparable DROP INDEX and CREATE INDEX statements would have to be encapsulated in a transaction in order to achieve the same effect. Also, DBCC DBREINDEX() is easier for the server to optimize than a query featuring analogous DROP and CREATE INDEX statements. Here’s the source to sp_rebuildindexes_all:
USE master
IF OBJECT_ID(’sp_rebuildindexes_all’) IS NOT NULL
DROP PROC sp_rebuildindexes_all
GO
IF OBJECT_ID(’sp_rebuildindexes’) IS NOT NULL
DROP PROC sp_rebuildindexes
GO
CREATE PROC sp_rebuildindexes @tablename sysname=’%’
AS
SET NOCOUNT ON
DECLARE @execstr varchar(8000)
DECLARE Tables CURSOR FOR
-- Tried to use INFORMATION_SCHEMA.TABLES here but it refused to work
SELECT name
FROM sysobjects
-- Exclude views and system tables
WHERE OBJECTPROPERTY(OBJECT_ID(name),’IsUserTable’)=1
AND name LIKE @tablename
OPEN Tables
FETCH Tables INTO @tablename
WHILE (@@FETCH_STATUS=0) BEGIN
PRINT CHAR(13)+’Rebuilding indexes for: ’+@tablename
SET @execstr=’DBCC DBREINDEX(’+@tablename+’)’
EXEC(@execstr)
FETCH Tables INTO @tablename
END
CLOSE Tables
DEALLOCATE Tables
RETURN 0
GO
CREATE PROC sp_rebuildindexes_all @dbname sysname=’%’
/*
Object: sp_rebuildindexes_all
Description: Rebuilds the indexes for all tables in a given database or databases
Usage: sp_rebuildindexes_all [@dbname=Name of database to update (Default: "%")]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 4.2
Example: sp_rebuildindexes_all "pubs"
Created: 1991-09-12. Last changed: 1999-05-03.
*/
AS
SET NOCOUNT ON
IF (@dbname=’/?’) GOTO Help
DECLARE Databases CURSOR FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE NOT (CATALOG_NAME IN (’tempdb’,’master’,’msdb’,’model’)) -- Omit system DBs
AND CATALOG_NAME LIKE @dbname
DECLARE @execstr varchar(8000), @tablename sysname
OPEN Databases
FETCH Databases INTO @dbname
IF (@@FETCH_STATUS<>0) BEGIN -- No matching databases
CLOSE Databases
DEALLOCATE Databases
PRINT ’No databases were found that match "’+@dbname+’"’
GOTO Help
END
WHILE (@@FETCH_STATUS=0) BEGIN
PRINT CHAR(13)+’Rebuilding indexes in database: ’+@dbname
PRINT CHAR(13)
-- Prefixing DB name temporarily changes current DB
SET @execstr=’EXEC ’+@dbname+’..sp_rebuildindexes’
EXEC(@execstr)
FETCH Databases INTO @dbname
END
CLOSE Databases
DEALLOCATE Databases
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_rebuildindexes_all’,
@desc=’Rebuilds the indexes for all tables in a given database or databases’,
@parameters=’[@dbname=Name of database to update (Default: "%")]’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’4’, @revision=’2’,
@datecreated=’19910912’, @datelastchanged=’19990503’,
@example=’sp_rebuildindexes_all "pubs"’
RETURN -1
GO
sp_rebuildindexes_all
Rebuilding indexes for: authors
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Rebuilding indexes for: jobs
Index (ID = 1) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Rebuilding indexes for: publishers
Index (ID = 1) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Rebuilding indexes for: roysched
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Rebuilding indexes for: sales
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
Index (ID = 3) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Rebuilding indexes for: stores
Index (ID = 1) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Rebuilding indexes for: titleauthor
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
Index (ID = 3) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Rebuilding indexes for: titles
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
Index (ID = 3) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Note the use of the sp_rebuildindexes stored procedure to call DBCC DBREINDEX() Why is this? Why didn’t we just call DBREINDEX() from the main procedure? Why do we need a second routine? We need sp_rebuildindexes in order to change the database context temporarily so that the Transact-SQL actually performing the reindex runs in the correct database. Prefixing a call to a system procedure (one beginning with “sp_” and residing in the master database) with a database name—any database name—changes the database context for the duration of the procedure. It’s tantamount to issuing a USE dbname just prior to calling the procedure and then returning to the original database afterward.
Enterprise Manager includes a nice facility for scheduling and managing database backups. Since scheduled jobs can also be run ad hoc, you should normally use this facility to execute and manage your backups.
That said, there may be times when you want to perform backups using Transact-SQL. You may have other code that needs to execute immediately prior to the backup, you might need to create backups on alternate media or with different options, or you may have some other compelling reason for making backups this way—there are a number of situations where this might be the case. Here’s a procedure that automates the task of backing up all the databases on a server:
USE master
GO
IF OBJECT_ID(’sp_dbbackup’) IS NOT NULL
DROP PROC sp_dbbackup
GO
CREATE PROC sp_dbbackup @dbname sysname=’%’,
@server sysname=’(local)’, @username sysname=NULL, @password sysname="
/*
Object: sp_dbbackup
Description: Backups up one or more databases, creating backup devices as needed
Usage: sp_dbbackup [@dbname=database name or mask to backup (Default: ’%’)],
[,@server="server name"][, @username="user name"][, @password="password"]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 7.01
Example: sp_dbbackup ’m%’ -- Backs up all databases whose names begin with ’m’
Created: 1990-01-07. Last changed: 1999-07-03.
*/
AS
SET NOCOUNT ON
IF (@dbname=’/?’) GOTO Help
IF (@username IS NULL) SET @username=SUSER_SNAME()
-- Create backup devices and backup each database (except tempdb)
DECLARE @rootpath sysname, @devname sysname, @execstr varchar(8000), @logmessage
varchar(8000)
-- Get SQL Server root installation path
EXEC sp_getSQLregistry @regkey=’SQLRootPath’, @regvalue=@rootpath OUTPUT,
@server=@server,
@username=@username, @password=@password
DECLARE Databases CURSOR FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME <> ’tempdb’ -- Omit system DBs
AND CATALOG_NAME LIKE @dbname
ORDER BY CATALOG_NAME
OPEN Databases
FETCH Databases INTO @dbname
SET @devname=@dbname+’back’
WHILE (@@FETCH_STATUS=0) BEGIN
IF NOT EXISTS(
SELECT * FROM master..sysdevices WHERE name = @dbname+’back’)
BEGIN
-- Create the data backup device
PRINT CHAR(13)+’Adding the data backup device for: ’+@dbname
SET @execstr=’EXEC sp_addumpdevice ’’disk’’,"’+@dbname+’back’+’", "’+
@rootpath+’ackup’+@dbname+’back.dmp"’
EXEC(@execstr)
END
-- Backup the database
PRINT CHAR(13)+’Backing up database ’+@dbname
BACKUP DATABASE @dbname TO @devname
SET @logmessage=’Backup of database ’+@dbname+’ complete’
EXEC master..xp_logevent 60000, @logmessage, ’INFORMATIONAL’
-- Backup its log
IF (@dbname<>’master’) AND (DATABASEPROPERTY(@dbname,’IsTruncLog’)=0)
BEGIN
IF NOT
EXISTS(SELECT * FROM master..sysdevices WHERE name = @dbname+’back’)
BEGIN
-- Create the log backup device
PRINT ’Adding the log backup device for: ’+@dbname
SET @execstr=’EXEC sp_addumpdevice ’’disk’’, "’+ @dbname +
’logback’+’", "’
+@rootpath+’ackup’+@dbname+’logback.dmp"’
EXEC(@execstr)
END
PRINT ’Backing up the transaction log for: ’+@dbname
SET @devname=@dbname+’logback’
BACKUP LOG @dbname TO @devname
SET @logmessage=’Backup of the transaction log for database ’+
@dbname+’ complete’
EXEC master..xp_logevent 60000, @logmessage, ’INFORMATIONAL’
END
FETCH Databases INTO @dbname
SET @devname=@dbname+’back’
END
CLOSE Databases
DEALLOCATE Databases
PRINT CHAR(13)+’Backup operation successfully completed’
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_dbbackup’, @desc=’Backups up one or more databases,
creating backup devices as needed’,
@parameters=’[@dbname=database name or mask to backup (Default: ’’%’’)]
[,@server="server name"][, @username="user name"][, @password="password"]’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’7’,@revision=’01’,
@datecreated=’19900107’, @datelastchanged=’19990703’,
@example=’sp_dbbackup ’’m%’’ -- Backs up all databases whose names begin with ’’m’’ ’
RETURN -1
GO
sp_dbbackup
Backing up database GVM
Processed 824 pages for database ’GVM’, file ’GVM_Data’ on file 30.
Processed 1 pages for database ’GVM’, file ’GVM_Log’ on file 30.
Backup or restore operation successfully processed 825 pages in 2.468 seconds
(2.735 MB/sec).
Backing up the transaction log for: GVM
Processed 1 pages for database ’GVM’, file ’GVM_Log’ on file 14.
Backup or restore operation successfully processed 1 pages in 0.086 seconds
(0.011 MB/sec).
Backing up database master
Processed 1264 pages for database ’master’, file ’master’ on file 21.
Processed 1 pages for database ’master’, file ’mastlog’ on file 21.
Backup or restore operation successfully processed 1265 pages in 3.302 seconds
(3.136 MB/sec).
Backing up database model
Processed 96 pages for database ’model’, file ’modeldev’ on file 18.
Processed 1 pages for database ’model’, file ’modellog’ on file 18.
Backup or restore operation successfully processed 97 pages in 0.433 seconds
(1.818 MB/sec).
Backing up database msdb
Processed 936 pages for database ’msdb’, file ’MSDBData’ on file 17.
Processed 1 pages for database ’msdb’, file ’MSDBLog’ on file 17.
Backup or restore operation successfully processed 937 pages in 2.369 seconds
(3.237 MB/sec).
Backing up database Northwind
Processed 392 pages for database ’Northwind’, file ’Northwind’ on file 17.
Processed 1 pages for database ’Northwind’, file ’Northwind_log’ on file 17.
Backup or restore operation successfully processed 393 pages in 1.113 seconds
(2.886 MB/sec).
Adding the data backup device for: Northwind2
’Disk’ device added.
Backing up database Northwind2
Processed 112 pages for database ’Northwind2’, file ’Northwind2sys’ on file 1.
Processed 16 pages for database ’Northwind2’, file ’Northwind2data’ on file 1.
Processed 1 pages for database ’Northwind2’, file ’Northwind2log’ on file 1.
Backup or restore operation successfully processed 129 pages in 0.591 seconds
(1.775 MB/sec).
Backing up database pubs
Processed 248 pages for database ’pubs’, file ’pubs’ on file 18.
Processed 1 pages for database ’pubs’, file ’pubs_log’ on file 18.
Backup or restore operation successfully processed 249 pages in 0.770 seconds
(2.639 MB/sec).
Backup operation successfully completed
The procedure does a couple of interesting things. First, it not only performs backups but also creates backup devices as needed. It uses the sp_getSQLregistry procedure (introduced in Chapter 19) to query the system registry for SQL Server’s root path and constructs a physical device location using this path. The fact that it automatically creates devices is one advantage this routine has over a backup scheduled via Enterprise Manager.
Another interesting element of the procedure is the use of DATABASEPROPERTY() to determine whether a database has been configured with the trunc. log on chkpt option. It needs to know this in order to avoid attempting to back up such a database’s transaction log, since this would result in an error. Backing up a transaction log that has been truncated by the system would be nonsensical—the backup would be useless—and the server will prohibit you from doing so. Once trunc. log on chkpt is enabled, your only option for backing up a database is to back up the entire database.
The ability to execute operating system commands is a very powerful extension to the Transact-SQL language. This, coupled with its OLE automation support, allows Transact-SQL to perform the kinds of tasks normally reserved for traditional programming languages. The procedure below uses the extended procedure xp_cmdshell to copy an operating system file. It accepts operating system wildcards, so it can copy more than one file at a time. Here’s the code:
USE master
IF OBJECT_ID(’sp_copyfile’) IS NOT NULL
DROP PROC sp_copyfile
GO
CREATE PROCEDURE sp_copyfile @sourcefilepath sysname, @targetfilepath sysname=NULL
/*
Object: sp_copyfile
Description: Copies an operating system file
Usage: sp_copyfile @sourcefilepath=full source file path, @targetfilepath=target file
path and/or filename
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 6.0
Example: sp_copyfile ’c:mssql7ackupmasterback.dmp’
’c:mssql7ackupmasterback.dmp.copy’
sp_copyfile ’c:mssql7ackupmasterback.dmp’ ’\archiveserverd$ackups’
sp_copyfile ’c:mssql8ackup*.dmp’ ’g:databasedumps’
Created: 1995-12-19. Last changed: 1999-06-02.
*/
AS
SET NOCOUNT ON
IF (@sourcefilepath=’/?’) OR (@targetfilepath IS NULL) GOTO Help
DECLARE @cmdstr varchar(8000)
CREATE TABLE #cmd_result (output varchar(8000))
EXEC master..xp_sprintf @cmdstr OUTPUT, ’copy %s %s’,@sourcefilepath, @targetfilepath
INSERT #cmd_result
EXEC master..xp_cmdshell @cmdstr
SELECT * FROM #cmd_result
IF EXISTS(SELECT * FROM #cmd_result WHERE output like ’%file(s) copied%’) BEGIN
SET @cmdstr=’The file copy operation "’+@cmdstr+’" was successful (at least one file
was copied)’
PRINT @cmdstr
EXEC master..xp_logevent 60000, @cmdstr, ’INFORMATIONAL’
END ELSE RAISERROR(’File copy failed’,16,1)
DROP TABLE #cmd_result
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_copyfile’,@desc=’Copies an operating system file’,
@parameters=’@sourcefilepath=full source file path, @targetfilepath=target file path
and/or filename’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’6’,@revision=’0’,
@datecreated=’19951219’,@datelastchanged=’19990602’,
@example=’sp_copyfile ’’c:mssql7ackupmasterback.dmp’’
’’c:mssql7ackupmasterback.dmp.copy’’
sp_copyfile ’’c:mssql7ackupmasterback.dmp’’ ’’\archiveserverd$ackups’’
sp_copyfile ’’c:mssql8ackup*.dmp’’ ’’g:databasedumps’’ ’
GO
sp_copyfile ’c:mssql7logerrorlog’, ’c:mssql7logerrorlog.sav’
--------------------------------------------------------------------------------
1 file(s) copied.
The file copy operation "copy c:mssql7logerrorlog
c:mssql7logerrorlog.sav"
was successful (at least one file was copied)
This routine uses xp_sprintf to set up the operating system COPY command before executing it. We could have created the command through simple string concatenation, but I’ve used xp_sprintf here to highlight its availability and usefulness. It provides functionality very similar to that of the C/C++ sprintf() function and can come in quite handy, especially when your formatting needs are more complex than those presented here. Unfortunately, it supports only string arguments at present, but you can cast other types of variables as strings in order to pass them to it.
Note the use of the database prefix on both the call to xp_sprintf and the call to xp_cmdshell. This is mandatory because, unlike regular system procedures, extended procedures aren’t automatically located across databases. Failing to qualify fully a call to an extended procedure will result in that call failing from any database except master.
Unless its no_output option is specified, xp_cmdshell returns a result set containing the output of the operating system command(s) it executes. In this case, sp_copyfile uses INSERT ...EXEC to place this output in a table so that it can be scanned to see whether the operation succeeded. We need to find the string “file(s) copied” in order to ensure that at least one file was successfully copied. The routine uses the EXISTS predicate to determine whether the string appears in the xp_cmdshell output and displays the appropriate message.
The need for portable databases has grown increasingly over the last few years. Networks have gotten faster, hard drives have gotten bigger, and machines have gotten cheaper to the point that it’s common to see file transfers and email attachments several megabytes in size. It’s not uncommon to see whole databases attached to an email.
SQL Server provides the sp_create_removable stored procedure for the express purpose of creating portable—that is, movable—databases. Sp_make_portable uses this procedure to automate the process of making a portable copy of an existing database. You pass in a database name, and sp_make_portable creates a portable database containing the same objects as the original (without data). This database can then be taken off line and copied onto removable media, emailed, transferred to another server, and so on. Here’s the code:
USE master
IF OBJECT_ID(’sp_make_portable’) IS NOT NULL
DROP PROC sp_make_portable
GO
CREATE PROC sp_make_portable @dbname sysname=NULL, @newdbname sysname=NULL,
@objectname sysname=’%’,
@username sysname=NULL, @password sysname=", @server sysname=’(local)’
/*
Object: sp_make_portable
Description: Makes a portable copy of an existing database (schema only - no data)
Usage: sp_make_portable @newdbname=name of new database to create
[,@dbname=database to copy (Default: DB_NAME())]
[,@objectname=mask specifying which objects to copy (Default "%")]
[,@username=user account to use for SQL-DMO (Default: SUSER_SNAME()]
[,@password=password for DMO user account (Default: "")]
[,@server=server to log into (Default: "(local)")]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 7.0
Example: sp_make_portable @dbname="northwind", @newdbname="northwind2", @user="sa"
Created: 1996-08-03. Last changed: 1999-07-03.
*/
AS
SET NOCOUNT ON
IF (@dbname=’/?’) OR (@newdbname=’/?’) OR (@newdbname IS NULL) GOTO Help
DECLARE @workstr varchar(8000), @sqlpath varchar(8000), @scriptfile sysname, @res int,
@sysdevp sysname, @datadevp sysname, @logdevp sysname,
@sysdevl sysname, @datadevl sysname, @logdevl sysname
-- Default to copying the current database
IF (@dbname IS NULL) SET @dbname=DB_NAME()
-- Use the current user’s login name for DMO
IF (@username IS NULL) SET @username=SUSER_SNAME()
IF (DB_ID(@dbname) IS NULL) GOTO Help -- Invalid source database name
EXEC @res=sp_validname @newdbname,0 -- Very rudimentary -- doesn’t do much
IF (@res=1) GOTO Help
-- Get rid of target database if it already exists
IF (DB_ID(@newdbname) IS NOT NULL)
EXEC sp_dbremove @newdbname,DROPDEV
-- Get SQL Server’s default installation path
EXEC sp_getSQLregistry ’SQLRootPath’,@sqlpath OUTPUT,
@username=@username, @password=@password, @server=@server
EXEC master..xp_sprintf @workstr OUTPUT, ’DEL %s\data\%s.*’,
@sqlpath,@newdbname
-- Delete the operating system files for the target DB
EXEC master..xp_cmdshell @workstr, no_output
SET @sysdevl=@newdbname+’sys’ -- Define logical and physical device names
SET @datadevl=@newdbname+’data’ -- based on the name of the new database
SET @logdevl=@newdbname+’log’
SET @sysdevp=@sqlpath+’data’+@newdbname+’.sdf’
SET @datadevp=@sqlpath+’data’+@newdbname+’.mdf’
SET @logdevp=@sqlpath+’data’+@newdbname+’.ldf’
EXEC master..sp_create_removable -- Build the new database
@dbname=@newdbname,
@syslogical=@sysdevl,
@sysphysical=@sysdevp,
@syssize=1,
@loglogical=@logdevl,
@logphysical=@logdevp,
@logsize=1,
@datalogical1=@datadevl,
@dataphysical1=@datadevp,
@datasize1=3
/*
-- Commented out because sp_certify_removable is (7/3/99, SQL 7 SP1) apparently broken.
It reports:
-- Server: Msg 208, Level 16, State 1, Procedure sp_check_portable, Line 18
-- Invalid object name ’sysdatabases’.
-- when called in the following manner:
EXEC @res=master..sp_certify_removable @newdbname, auto -- Ensure that the
new DB is portable
IF (@res<>0) BEGIN
RAISERROR(’Error creating portable database. Database files
sp_certify_removable check’,16,1)
DECLARE @filename sysname
SET @filename = ’CertifyR_[’+@newdbname+’].txt’
EXEC sp_readtextfile @filename
RETURN -1
END
EXEC master..sp_dboption @newdbname,’offline’,false -- Set database back online
*/
EXEC master..xp_sprintf @workstr OUTPUT,’EXEC %s..sp_generate_script @objectname="%s",
@outputname="%s\%sTEMP.SQL",
@resultset="NO", @username="%s", @password="%s", @server="%s"’,
@dbname,@objectname,@sqlpath,@newdbname, @username, @password, @server
EXEC(@workstr) -- Generate a script for the old database
EXEC master..xp_sprintf @workstr OUTPUT,’osql -U%s -P%s -S%s -d%s -
i%s\%sTEMP.SQL -o%s\%sTEMP.OUT’,
@username,@password,@server,@newdbname,@sqlpath, @newdbname, @sqlpath, @newdbname
-- Run the script _in the new database_
EXEC master..xp_cmdshell @workstr, no_output
PRINT REPLICATE(’-’,256)+CHAR(13)+
’Removable database ’+@newdbname+’ successfully created’
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_make_portable’,
@desc=’Makes a portable copy of an existing database (schema only - no data)’,
@parameters=’@newdbname=name of new database to create
[,@dbname=database to copy (Default: DB_NAME())]
[,@objectname=mask specifying which objects to copy]
[,@username=user account to use for SQL-DMO (Default: SUSER_SNAME()]
[,@password=password for DMO user account (Default: "")]
[,@server=server to log into (Default: "(local)")]’,
@author=’Ken Henderson’, @email=’[email protected]’,@version=’7’,@revision=’0’,
@datecreated=’19960803’, @datelastchanged=’19990703’,
@example=’sp_make_portable @dbname="northwind", @newdbname="northwind2", @user="sa"’
RETURN -1
GO
sp_make_portable @dbname=’Northwind’, @newdbname=’Northwind2’
The CREATE DATABASE process is allocating 1.00 MB on disk ’Northwind2sys’.
The CREATE DATABASE process is allocating 1.00 MB on disk ’Northwind2log’.
Extending database by 3.00 MB on disk ’Northwind2data’.
The filegroup property ’DEFAULT’ has been set.
The first step in building the new database is to determine where SQL Server is installed so that we can build the names of the physical devices that will host it. Next, we call sp_create_removable to build the database. Once this happens, we call sp_generate_script (covered later in this chapter) to generate a SQL script for the entire source database; then we call OSQL (via xp_cmdshell) to execute it. We use OSQL’s -d command-line option to execute the script within the context of the new database instead of the original database. This ensures that the objects created by the script end up in the new database.
The end result of all this is a database that’s portable. Once your portable database is constructed, you can use sp_dboption to take it off line so that you can copy its operating system files elsewhere. You could even use sp_copyfile to copy them.
The system procedure sp_attach_db is used to make a portable database accessible from a new server. This is perfect for installing ready-made databases from removable media such as CD-ROMs. It presents a viable alternative to using scripts and backups to deploy databases with your applications.
One task that it pays to standardize and streamline as much as possible is that of setting up new servers. DBAs who set up database servers on a regular basis usually get the whole process down to a well-oiled routine. They’re able to do it in their sleep if they must—and sometimes they must.
The script presented below represents a template from which you can construct such a routine of your own. It’s certainly not comprehensive—it’s likely that each shop will have its own setup requirements and needs. The template isn’t provided as a stored procedure because the first thing you’d have to do to with a stored procedure is load a script on the new server to create it. Given that server initialization is usually a one-time thing, you might as well just use a script in the first place.
The kinds of things that you typically find in server initialization scripts are:
• Dump device construction
• Creation of user databases and/or restore operations to populate them
• Custom stored procedure installation
• Autostartup procedure specification
• Template database (model) setup
• Maintenance job scheduling
• User account and security setup
• Database and database option configuration
• Server configuration
Getting the server set up correctly to begin with is essential if you want it to behave itself down the road. Even though the DBA’s workload has been reduced with each successive release of SQL Server, planning is everything. Even moderately used systems require some degree of management.
Here’s an example of a server initialization script:
/*
Object: INIT_SERVER.SQL
Description: Server initialization script
Created by: Ken Henderson. Email: [email protected]
Version: 7.0
Created: 1990-02-06. Last changed: 1999-07-05.
*/
SET NOCOUNT ON
GO
USE master
GO
DECLARE @username sysname, @password sysname, @server sysname
SET @username=’sa’ -- Put the login you want to use here
SET @password=" -- Put your password here (be sure this
-- script is stored in a secure location!)
SET @server=’(local)’ -- Put your server name here
-- Set template database options
PRINT ’Setting template database options’
EXEC master..sp_dboption ’model’,’auto update statistics’,true
EXEC master..sp_dboption ’model’,’autoshrink’,true
EXEC master..sp_dboption ’model’,’select into/bulkcopy’,true
EXEC master..sp_dboption ’model’,’torn page detection’,true
-- Add tempdate data types
PRINT ’Adding template data types’
IF EXISTS(SELECT * FROM model..systypes WHERE name = ’d’)
EXEC model..sp_droptype ’d’
EXEC model..sp_addtype ’d’, ’decimal(10,2)’,’NULL’
-- Create backup devices and job steps for every database except tempdb
PRINT ’Creating backup devices and job steps for every database except tempdb’
DECLARE @rootpath sysname, @execstr varchar(8000), @dbname sysname, @job_id
uniqueidentifier, @step_id int
-- Get SQL Server root installation path
EXEC sp_getSQLregistry @regkey=’SQLRootPath’, @regvalue=@rootpath OUTPUT,
@username=@username, @password=@password, @server=@server
-- Delete the operator if it already exists
IF EXISTS(SELECT * FROM msdb..sysoperators WHERE name = ’Puck Feet’)
EXEC msdb..sp_delete_operator ’Puck Feet’
-- Add the operator
PRINT ’Setting up the job operator’
EXEC msdb..sp_add_operator @name = ’Puck Feet’,
@enabled = 1,
@email_address =’[SMTP:[email protected]]’,
@pager_address = ’[email protected]’,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 210000,
@pager_days = 127,
@netsend_address=’NOT_HOCKEY’
-- Delete the job if it already exists
SELECT @job_id = job_id FROM msdb..sysjobs WHERE name=’DailyBackup’
IF (@job_id IS NOT NULL) BEGIN
-- Don’t delete if it’s a multi-server job
IF (EXISTS (SELECT * FROM msdb..sysjobservers WHERE (job_id=@job_id) AND (server_id
<> 0))) BEGIN
RAISERROR (’Unable to create job because there is already a multi-server job with
the same name.’,16,1)
END ELSE -- Delete the job
EXECUTE msdb..sp_delete_job @job_id=@job_id
END
-- Add the backup job
PRINT ’Adding the backup job’
EXEC msdb..sp_add_job @job_name = ’DailyBackup’,
@enabled = 1,
@description = ’Daily backup of all databases’,
@owner_login_name = ’sa’,
@notify_level_eventlog = 2,
@notify_level_netsend = 2,
@notify_netsend_operator_name=’Puck Feet’,
@delete_level = 0
-- Schedule the job
PRINT ’Scheduling the job’
EXEC msdb..sp_add_jobschedule @job_name = ’DailyBackup’,
@name = ’ScheduledBackup’,
@freq_type = 4, -- everyday
@freq_interval = 1,
@active_start_time = 101600
DECLARE Databases CURSOR FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME <> ’tempdb’ -- Omit system DBs
ORDER BY CATALOG_NAME
OPEN Databases
FETCH Databases INTO @dbname
SET @step_id=0
WHILE (@@FETCH_STATUS=0) BEGIN
IF NOT EXISTS(SELECT * FROM master..sysdevices WHERE name =
@dbname+’back’) BEGIN
-- Create the data backup device
PRINT ’Adding the data backup device for ’+@dbname
SET @execstr=’EXEC sp_addumpdevice ’’disk’’, "’+@dbname+’back’+’",
"’+@rootpath+’ackup’+@dbname+’back.dmp"’
EXEC(@execstr)
END
-- Add a job step to backup the database
PRINT ’Adding the database backup job step for ’+@dbname
SET @execstr=’EXEC msdb..sp_add_jobstep @job_name = ’’DailyBackup’’,
@step_name = "’+’Backup of database: ’+@dbname+’",
@subsystem = ’’TSQL’’,
@command = ’’BACKUP DATABASE ’+@dbname+’ TO ’+@dbname+’back’’,
@on_success_action=3’
EXEC(@execstr)
SET @step_id=@step_id+1
-- Add one to backup its log
IF (@dbname<>’master’) AND (DATABASEPROPERTY(@dbname,’IsTruncLog’)=0)
BEGIN
IF NOT EXISTS(SELECT * FROM master..sysdevices
WHERE name = @dbname+’back’) BEGIN
-- Create the log backup device
PRINT ’Adding the log backup device for ’+@dbname
SET @execstr=’EXEC sp_addumpdevice ’’disk’’,
"’+@dbname+’logback’+’", "’
+@rootpath+’ackup’+@dbname+’logback.dmp"’
EXEC(@execstr)
END
PRINT ’Adding the log backup job step for ’+@dbname
SET @execstr=’EXEC msdb..sp_add_jobstep @job_name = ’’DailyBackup’’,
@step_name = "’+’Backup of log for database: ’+@dbname+’",
@subsystem = ’’TSQL’’,
@command = ’’BACKUP LOG ’+@dbname+’ TO ’+@dbname+’logback’’,
@on_success_action=3’
EXEC(@execstr)
SET @step_id=@step_id+1
END
FETCH Databases INTO @dbname
END
CLOSE Databases
DEALLOCATE Databases
-- Set the last job step to quit with success
EXEC msdb..sp_update_jobstep @job_name=’DailyBackup’, @step_id=@step_id,
@on_success_action=1
-- Associate the job with the job server
EXEC msdb..sp_add_jobserver @job_name=’DailyBackup’
PRINT CHAR(13)+’Successfully initialized server’
GO
Setting template database options
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Adding template data types
Type has been dropped.
Type added.
Creating backup devices and job steps for every database except tempdb
Setting up the job operator
Adding the backup job
Scheduling the job
Adding the database backup job step for CM
Adding the log backup job step for CM
Adding the database backup job step for master
Adding the database backup job step for model
Adding the database backup job step for msdb
Adding the database backup job step for Northwind
Adding the database backup job step for Northwind2
Adding the database backup job step for PM
Adding the database backup job step for PO
Adding the database backup job step for pubs
Adding the database backup job step for VCDB
Successfully initialized server
This script does a number of interesting things. First, it sets up the model database, specifying a template set of data types and options. These parameters will be used for new databases when they’re created. They’ll also be used for tempdb when it’s rebuilt each time the server is cycled. So, for example, you could enable select into/bulk copy in model if you want it enabled in tempdb when the server starts. That said, an autostart custom procedure is probably a better option because it averts the risk of enabling select/into bulk copy by accident in other, newly created databases.
Next, the script uses sp_getSQLregistry to find SQL Server’s installation path, then builds backup devices as necessary using this path. It then sets up a SQL Server Agent job to back up each database (and its log, as appropriate), along with an operator and a schedule on which to run the job.
Each SQL Server Agent job is composed of job steps. A simple job might have just one step; more complex ones will have many. Here, we add a separate step to back up each database and each database’s log. We specify a default on_success_action of 3, which tells the Agent to proceed with the next step when a job step completes successfully. This doesn’t work for the final step of the job since there is no next step. Thus the script includes a call to sp_update_jobstep that tells the final job step simply to terminate the job when it successfully completes.
Note the call to sp_add_jobserver. This associates the newly created job with the local job server. Failing to do this results in a job that never runs. One would think that simply adding the job via sp_add_job would establish this link, but that’s not the case. The flexibility here—the separation of jobs from job servers—allows you to schedule jobs on other servers, a feature that’s quite useful to administrators managing multiserver environments. However, the cost of this flexibility is that you must remember to link your job with your job server when scheduling jobs via Transact-SQL. This is another good argument for using the GUI tools. You don’t have to worry about details like this when using Enterprise Manager to schedule jobs—it defaults scheduling jobs on the local server.
Text files are so ubiquitous in system administration that it’s no surprise that DBAs often need to be able to access them from SQL Server. Processing the output from operating system commands and SQL Server’s command-line utilities, perusing the error log, and loading SQL script files are just a few examples of the many dealings DBAs commonly have with text files. To that end, below is a procedure that reads a text file and returns it as a result set. Using INSERT...EXEC, you can place its output in a table for further processing or simply return it as a result set of your own, as the sp_generate_script procedure below demonstrates. Here’s the source code to sp_readtextfile:
USE master
IF OBJECT_ID(’sp_readtextfile’) IS NOT NULL
DROP PROC sp_readtextfile
GO
CREATE PROC sp_readtextfile @textfilename sysname
/*
Object: sp_readtextfile
Description: Reads the contents of a text file into a SQL result set
Usage: sp_readtextfile @textfilename=name of file to read
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 7.0
Example: sp_readtextfile ’D:MSSQL7LOGSerrorlog’
Created: 1996-05-01. Last changed: 1999-06-14.
*/
AS
SET NOCOUNT ON
IF (@textfilename=’/?’) GOTO Help
CREATE TABLE #lines (line varchar(8000))
EXEC(’BULK INSERT #lines FROM "’+@textfilename+’"’)
SELECT * FROM #lines
DROP TABLE #lines
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_readtextfile’,
@desc=’Reads the contents of a text file into a SQL result set’,
@parameters=’@textfilename=name of file to read’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’7’,@revision=’0’,
@datecreated=’19960501’, @datelastchanged=’19990614’,
@example=’sp_readtextfile ’’D:MSSQL7LOGSerrorlog’’ ’
RETURN -1
EXEC sp_readtextfile ’c:mssql7logerrorlog.sav’
(Results abridged)
line
----------------------------------------------------------------------------------------------------------------------
1999-07-06 09:10:41.14 kernel Microsoft SQL Server 7.00 - 7.00.699 (Intel X86)
May 21 1999 14:08:18
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 4.0 (Build 1381: Service Pack 4)
1999-07-06 09:10:41.25 kernel Copyright (C) 1988-1997 Microsoft Corporation.
1999-07-06 09:10:41.25 kernel All rights reserved.
1999-07-06 09:10:41.25 kernel Logging SQL Server messages in file
’d:MSSQL7logERRORLOG’.
1999-07-06 09:10:41.56 kernel initconfig: Number of user connections limited to 32767.
1999-07-06 09:10:41.56 kernel SQL Server is starting at priority class ’normal’(1 CPU
detected).
1999-07-06 09:10:41.70 kernel User Mode Scheduler configured for thread processing
1999-07-06 09:10:43.34 server Directory Size: 16215
1999-07-06 09:10:43.45 spid1 Using dynamic lock allocation. [500] Lock Blocks, [1000]
Lock Owner Blocks
1999-07-06 09:10:43.49 spid1 Starting up database ’master’.
1999-07-06 09:10:43.49 spid1 Opening file d:MSSQL7datamaster.mdf.
1999-07-06 09:10:43.73 spid1 Opening file d:MSSQL7datamastlog.ldf.
1999-07-06 09:10:44.23 spid1 Loading SQL Server’s Unicode collation.
1999-07-06 09:10:44.28 spid1 Loading SQL Server’s non-Unicode sort order and
character set.
1999-07-06 09:10:45.36 spid1 107 transactions rolled forward in database ’master’ (1).
1999-07-06 09:10:45.37 spid1 0 transactions rolled back in database ’master’ (1).
1999-07-06 09:10:51.28 spid1 Recovery complete.
1999-07-06 09:10:51.28 spid1 SQL Server’s Unicode collation is:
1999-07-06 09:10:51.28 spid1 ’English’ (ID = 1033).
1999-07-06 09:10:51.28 spid1 comparison style = 196609.
1999-07-06 09:10:51.28 spid1 SQL Server’s non-Unicode sort order is:
1999-07-06 09:10:51.28 spid1 ’nocase_iso’ (ID = 52).
1999-07-06 09:10:51.28 spid1 SQL Server’s non-Unicode character set is:
1999-07-06 09:10:51.28 spid1 ’iso_1’ (ID = 1).
The internal workings of this routine are pretty straightforward. It first loads the file supplied to it into a temporary table via BULK INSERT. Next, it issues a SELECT * against the temporary table to return its contents as a result set. The end result is that the caller receives the text file as a SQL Server result set.
Note
There’s a bug in the initial shipping version of SQL 7.0 that prevents sp_readtext from being called by routines that use the OLE Automation sp_OAxxxx procedures. Sp_readtext uses the Transact-SQL BULK INSERT command to load its text file into a temporary table, which it then returns as a result set. BULK INSERT is marked as a free threaded OLE provider. With the ODSOLE facility (the sp_OAxxxx procedures), COM is initialized using the single-apartment model. When BULK INSERT is called by a thread already initialized as a single apartment, the conflict between the two models causes the instantiation of the OLE-DB Stream provider to fail—BULK INSERT can’t read the operating system file that’s been passed to it.
The workaround requires modifying the system registry. Follow these steps to allow BULK INSERT to be called from procedures and scripts using the single-apartment COM model:
1. Run regedit.exe or regedt32.exe.
2. Drill down into HKEY_CLASSES_ROOTCLSID{F3A18EEA-D34B-11d2-88D7-00C04F68DC44}InprocServer32ThreadingModel.
3. Replace Free with Both.
A common administrative need is to be able to generate scripts for database objects. DBAs sometimes want these for extra backups, for making a duplicate of a database or an object, or for searching for some unusual coding technique or object definition.
Enterprise Manager provides a nice facility for scripting database objects, and it should be your tool of choice for doing so. It performs its magic by accessing SQL Server’s SQL-DMO (Distributed Management Objects) facility, a COM interface that provides server management facilities to applications. Since Transact-SQL provides access to COM servers via its ODSOLE facility (the sp_Oaxxx procedures), we can access SQL-DMO directly from SQL without going through Enterprise Manager. (Refer to Chapter 19 for more details on this technique.) Here’s a procedure that scripts objects directly from Transact-SQL:
USE master
GO
IF OBJECT_ID(’sp_generate_script’) IS NOT NULL
DROP PROC sp_generate_script
GO
CREATE PROC sp_generate_script
@objectname sysname=NULL, -- Object mask to copy
@outputname sysname=NULL, -- Output file to create (default: @objectname+’.SQL’)
@scriptoptions int=NULL, -- Options bitmask for Transfer
@resultset varchar(3)="YES", -- Determines whether the script is returned as a result set
@server sysname=’(local)’, -- Name of the server to connect to
@username sysname=’sa’, -- Name of the user to connect as (defaults to ’sa’)
@password sysname=NULL -- User’s password
/*
Object: sp_generate_script
Description: Generates a creation script for an object or collection of objects
Usage: sp_generate_script [@objectname="Object name or mask (defaults to all object in current
database)"]
[,@outputname="Output file name" (Default: @objectname+".SQL", or GENERATED_SCRIPT.SQL for
entire database)]
[,@scriptoptions=bitmask specifying script generation options]
[,@resultset="YES"|"NO" -- determines whether to return the script as a result set (Default:
"YES")]
[,@server="server name"][, @username="user name"][, @password="password"]
Returns: (None)
Created by: Ken Henderson. Email: [email protected]
Version: 2.0
Created: 1996-12-01. Last changed: 1999-06-06.
*/
AS
/* SQL-DMO constant variables omitted for brevity. They are included in the CD version.*/
DECLARE @dbname sysname,
@sqlobject int, -- SQL Server object
@object int, -- Work variable for accessing COM objects
@hr int, -- Contains HRESULT returned by COM
@tfobject int -- Stores pointer to Transfer object
IF (@objectname=’/?’) GOTO Help
SET @resultset=UPPER(@resultset)
IF (@objectname IS NOT NULL) AND (CHARINDEX(’%’,@objectname)=0) AND
(CHARINDEX(’_’,@objectname)=0) BEGIN
SET @dbname=ISNULL(PARSENAME(@objectname,3),DB_NAME()) -- Extract the DB
name; default to current
SET @objectname=PARSENAME(@objectname,1) -- Remove extraneous stuff from table name
IF (@objectname IS NULL) BEGIN
RAISERROR(’Invalid object name.’,16,1)
RETURN -1
END
IF (@outputname IS NULL)
SET @outputname=@objectname+’.SQL’
END ELSE BEGIN
SET @dbname=DB_NAME()
IF (@outputname IS NULL)
SET @outputname=’GENERATED_SCRIPT.SQL’
END
-- Create a SQLServer object
EXEC @hr=sp_OACreate ’SQLDMO.SQLServer’, @sqlobject OUTPUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @sqlobject, @hr
RETURN
END
-- Create a Transfer object
EXEC @hr=sp_OACreate ’SQLDMO.Transfer’, @tfobject OUTPUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Set Transfer’s CopyData property
EXEC @hr = sp_OASetProperty @tfobject, ’CopyData’, 0
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Tell Transfer to copy the schema
EXEC @hr = sp_OASetProperty @tfobject, ’CopySchema’, 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
IF (@objectname IS NULL) BEGIN -- Get all objects in the database
-- Tell Transfer to copy all objects
EXEC @hr = sp_OASetProperty @tfobject, ’CopyAllObjects’, 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Tell Transfer to get groups as well
EXEC @hr = sp_OASetProperty @tfobject, ’IncludeGroups’, 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Tell it to include users
EXEC @hr = sp_OASetProperty @tfobject, ’IncludeUsers’, 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Tell it to include logins
EXEC @hr = sp_OASetProperty @tfobject, ’IncludeLogins’, 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Include object dependencies, too
EXEC @hr = sp_OASetProperty @tfobject, ’IncludeDependencies’, 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
IF (@scriptoptions IS NULL)
SET @scriptoptions=@SQLDMOScript_OwnerQualify | @SQLDMOScript_Default |
@SQLDMOScript_Triggers | @SQLDMOScript_Bindings |
SQLDMOScript_DatabasePermissions | @SQLDMOScript_Permissions |
@SQLDMOScript_ObjectPermissions | @SQLDMOScript_ClusteredIndexes |
@SQLDMOScript_Indexes | @SQLDMOScript_Aliases | @SQLDMOScript_DRI_All |
@SQLDMOScript_IncludeHeaders
END ELSE BEGIN
DECLARE @obname sysname,
@obtype varchar(2),
@obowner sysname,
@OBJECT_TYPES varchar(30),
@obcode int
-- Used to translate sysobjects.type into the bitmap that Transfer requires
SET @OBJECT_TYPES=’T V U P D R TR ’
-- Find all the objects that match the mask and add them to Transfer’s
-- list of objects to script
DECLARE ObjectList CURSOR FOR
SELECT name,type,USER_NAME(uid) FROM sysobjects
WHERE (name LIKE @objectname)
AND (CHARINDEX(type+’ ’,@OBJECT_TYPES)<>0)
AND (OBJECTPROPERTY(id,’IsSystemTable’)=0)
AND (status>0)
UNION ALL -- Include user-defined data types
SELECT name,’T’,USER_NAME(uid)
FROM SYSTYPES
WHERE (usertype & 256)<>0
AND (name LIKE @objectname)
OPEN ObjectList
FETCH ObjectList INTO @obname, @obtype, @obowner WHILE (@@FETCH_STATUS=0) BEGIN
SET @obcode=POWER(2,(CHARINDEX(@obtype+’ ’,@OBJECT_TYPES)/3))
EXEC @hr = sp_OAMethod @tfobject, ’AddObjectByName’, NULL, @obname, @obcode, @obowner
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
FETCH ObjectList INTO @obname, @obtype, @obowner END
CLOSE ObjectList
DEALLOCATE ObjectList
IF (@scriptoptions IS NULL)
-- Keep it simple when not scripting the entire database
SET @scriptoptions=@SQLDMOScript_Default
END
-- Set Transfer’s ScriptType property
EXEC @hr = sp_OASetProperty @tfobject, ’ScriptType’, @scriptoptions
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Connect to the server
IF (@password IS NOT NULL) AND (@password<>’’)
EXEC @hr = sp_OAMethod @sqlobject, ’Connect’, NULL, @server, @username, @password
ELSE
EXEC @hr = sp_OAMethod @sqlobject, ’Connect’, NULL, @server, @username
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @sqlobject, @hr
RETURN
END
-- Get a pointer to the SQLServer object’s Databases collection
EXEC @hr = sp_OAGetProperty @sqlobject, ’Databases’, @object OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @sqlobject, @hr
RETURN
END
-- Get a pointer from the Databases collection for the specified database
EXEC @hr = sp_OAMethod @object, ’Item’, @object OUT, @dbname
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
PRINT ’Ignore the code displayed below. It’s a remnant of the SQL-DMO method used to produce
the script file’
-- Call the Database object’s ScriptTransfer method to create the script
EXEC @hr = sp_OAMethod @object, ’ScriptTransfer’,NULL, @tfobject, 2, @outputname
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
EXEC sp_OADestroy @sqlobject -- For cleanliness
EXEC sp_OADestroy @tfobject -- For cleanliness
IF (@resultset="YES") EXEC sp_readtextfile @outputname
RETURN 0
Help:
EXEC sp_usage @objectname=’sp_generate_script’,@desc=’Generates a creation script for an
object or collection of objects’,
@parameters=’[@objectname="Object name or mask (defaults to all object in current
database)"][,@outputname="Output file name" (Default: @objectname+".SQL", or
GENERATED_SCRIPT.SQL for entire database)]
[,@scriptoptions=bitmask specifying script generation options]
[,@server="server name"][, @username="user name"][, @password="password"]’,
@author=’Ken Henderson’, @email=’[email protected]’,
@version=’7’, @revision=’0’,
@datecreated=’19980401’, @datelastchanged=’19990702’,
@example=’sp_generate_script @objectname=’’authors’’, @outputname=’’authors.sql’’ ’
RETURN -1
GO
EXEC sp_generate_script ’authors’
line
----------------------------------------------------------------------------------------------------------------------------------------------------------------
set quoted_identifier OFF
GO
NULL
CREATE TABLE [authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) NOT NULL ,
[au_fname] [varchar] (20) NOT NULL ,
[phone] [char] (12) NOT NULL CONSTRAINT [DF__authors__phone__09DE7BCC] DEFAULT (’UNKNOWN’),
[address] [varchar] (40) NULL ,
[city] [varchar] (20) NULL ,
[state] [char] (2) NULL ,
[zip] [char] (5) NULL ,
[contract] [bit] NOT NULL ,
CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED
(
[au_id]
) ON [PRIMARY] ,
CHECK (([au_id] like ’[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]’)),
CHECK (([zip] like ’[0-9][0-9][0-9][0-9][0-9]’))
)
This code exhibits a number of interesting techniques. Let’s go through a few of them.
The procedure begins by instantiating the DMO SQLServer and Transfer objects. DMO’s SQLServer object is its root level access path—you use it to connect to the server and to access other objects on the server. The Transfer object encapsulates DMO’s server-to-server or server-to-file object and data transfer facility. Sp_generate_script uses it to generate SQL scripts.
Once Transfer is created, the procedure determines whether the user wants to script the entire database or only selected objects. This distinction is important because DMO lists objects in order of dependency when scripting an entire database. If only a subset of the objects in a database is to be scripted, the procedure opens a cursor on the sysobjects and systypes tables (via UNION ALL) and calls Transfer’s AddObjectByName method to set them up for scripting, one by one.
The procedure next uses the SQLServer object to locate the database housing objects it needs to script. It finds this database by accessing the object’s Databases collection. DMO objects often expose collections of other objects. Items in these collections can be accessed by name or by ordinal index. In the case of sp_generate_script, collection items are always accessed by name.
Once the procedure retrieves a pointer to the correct database, it calls that database’s ScriptTransfer method, passing it the previously created Transfer object as a parameter. This generates a SQL script containing the objects we’ve specified.
The final step in the procedure is to return the script as a result set. Usually, the caller will expect to see the script immediately. If @resultset = “YES” (the default), sp_generate_script calls sp_readtextfile to return the newly generated script file to the caller via a result set. A useful variation of this would be to return a cursor pointer to the script.
Though it was sometimes the only method for getting the job done in earlier releases of SQL Server, the need to use Transact-SQL to perform administrative tasks has lessened as the GUI tools have improved. This means that you should make using the GUI tools your default mode of operation for managing your servers. That said, there may be times when your needs exceed the capabilities of Enterprise Manager and the other GUI tools. When that happens, you can use the routines presented in this chapter, along with procedures and scripts you write yourself, to fill in the gaps left by the other tools.