The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

ADDEXTENDEDPROC(procname,DLL)

Adds an extended procedure to the list maintained by the server. It has the same basic functionality as the sp_addextendedproc stored procedure and is, in fact, called by the procedure. The procname parameter is the name of the extended procedure and DLL is the name of the DLL in which it resides.

DBCC
ADDEXTENDEDPROC(''xp_mode'',''xp_stats.dll'')

ADDINSTANCE(object,instance)

Adds an object instance to track in Performance Monitor. Stored procedures that initialize Performance Monitor counters use this to set up various areas of SQL Server for performance tracking. object is the name of the object that contains the instance (e.g., "SQL Replication Agents"). instance is the name of the instance to add (e.g., "Logreader").

DBCC ADDINSTANCE("SQL Replication Agents",
"Snapshot")

AUDITEVENT(id, subclass, succeeded, loginame, username, grpname, sid)

Generates a Security Audit trace event. You can trace Security Audit events in Profiler. Id is the event number you wish to generate. Subclass is the ID of the specific event subclass you wish to generate. Loginame is the login attempting the audited event. Username is the user name of the database user attempting the event.

Grpname is the name of the group or role to which the user belongs. Sid is the security ID number of the login attempting the audited action.

DBCC AUDITEVENT (109, 1, 0, @loginame,
@name_in_db, @grpname , NULL)
/*
Valid id/subclass values:
ID      Sub   Event
104     1     Add login
104     2     Drop login
105     1     Grant login
105     2     Revoke login
105     3     Deny login
106     1     Change default database
106     2     Change default language
107     1     User change password
107     2     Admin. change password
108     1     Add server role member
108     2     Drop server role member
109     1     Add DB user
109     2     Drop DB user
109     3     Grant DB access
109     4     Revoke DB access
110     1     Add DB role member
110     2     Drop DB role member
110     3     Change DB role member
111     1     Add role
111     2     Drop role
112     1     App role change password
*/

BCPTABLOCK(dbid, tabid, setflag)

Sets the table lock on bulk load option for a table. (It''s called by sp_tableoption.)

This can improve performance for bulk inserts because it avoids setting a row lock for every inserted row. Dbid is the database ID, tabid is the table''s object ID, and setflag is a 1 or 0 indicating whether to set the option.

DECLARE @dbid int, @objid int
SELECT @dbid=DB_ID(''pubs''),
@objid=OBJECT_ID(''titles'')
DBCC BCPTABLOCK(@dbid,@objid,1)

BUFFER(dbid[,objid][,numberofbuffers][,printopt { 0 | 1 | 2}])

Used to dump the contents of SQL Server memory buffers. Buffers can be listed for a specific object or for an entire database.

DECLARE @dbid int, @objid int
SELECT @dbid=DB_ID(''pubs''),
@objid=OBJECT_ID(''pubs..titles'')
SELECT COUNT(*) FROM pubs..titles -- Load
buf
DBCC TRACEON(3604)
DBCC BUFFER(@dbid,@objid,1,2)

BYTES(startingaddress,length)

Lists the contents of the memory area beginning at startingaddress for length bytes. The address specified must be a valid address within the SQL Server process space.

DBCC BYTES(0014767000,50)

CALLFULLTEXT(funcid[ catid][,objid]) Valid function ID values

USE master
GO
IF OBJECT_ID(''sp_fulltext_resource'') IS NOT
NULL
DROP PROC sp_fulltext_resource
GO
CREATE PROC sp_fulltext_resource @value
int -- value for ''resource_usage''
AS
DBCC CALLFULLTEXT(9,@value)
-- FTSetResource (@value)
IF (@@error<>0) RETURN 1
-- SUCCESS --
RETURN 0      --   sp_fulltext_resource
GO
EXEC sp_MS_marksystemobject
''sp_fulltext_resource''
EXEC sp_fulltext_resource 3

DBCONTROL(dbname,option)

Sets database options. Performs many of the functions of sp_dboption and is, in fact, called by the procedure. Dbname is the name of the database. Option is a token specifying the option to set.

/* Supported options
multi Specifies multi-user mode
offline      Takes database offline
online Brings database back online
readonly     Makes database readonly
readwrite    Makes database readwrite
single Specifies single-user mode
*/
DBCC DBCONTROL(''pubs'',multi)

DBINFO(dbname)

Lists system-level information about the specified database, including its creation date, ID, status, next timestamp value, and so forth.

DBCC DBINFO(''pubs'')

DBRECOVER(dbname)

Manually recovers a database. Normally, databases are recovered at system start-up. If this did not occurbecause of an error or because recovery was disabled (see trace flags 3607 and 3608DBCC

DBRECOVER can be used to attempt a manual recovery. Dbname is the name of the database to recover.

DBCC DBRECOVER(''pubs'')

DBREINDEXALL(dbname)

Rebuilds all the indexes in the current database. Only works for user (nonsystem) databases

DBCC DBREINDEXALL(''pubs'') WITH NO_INFOMSGS

DBCC DBTABLE(dbid)

Lists DBT (DB Table) and FCB (File Control Block) information for the specified database.

DECLARE @dbid int
SET @dbid=DB_ID(''pubs'')
DBCC DBTABLE(@dbid)

DELETEINSTANCE(object,instance)

Deletes a Performance Monitor object instance previously set up with DBCC ADDINSTANCE. Object is the name of the Performance Monitor object. Instance is the name of the instance to delete. Specify a wildcard for instance to delete multiple instances.

DBCC DELETEINSTANCE("SQL Replication
Merge", "%")

DES(dbid,objid)

Lists system-level descriptive information for the specified object.

DECLARE @dbid int, @objid int
SELECT @dbid=DB_ID(''pubs''),
@objid=OBJECT_ID(''authors'')
DBCC DES(@dbid, @objid)

DETACHDB(dbname)

Detaches a database from the server. The database can then be moved to another server and can be reattached with sp_attach_db. This function is called by the sp_detach_db stored procedure.

DBCC DETACHDB(''northwind2'')

DROPEXTENDEDPROC(procname)

Drops an extended procedure. It''s called by sp_dropextendedprocedure.

USE master
DBCC DROPEXTENDEDPROC(''xp_mode'')

ERRORLOG

Closes the current error log and starts another one, cycling the file extensions similarly to a server restart. It''s called by the sp_cycle_errorlog stored procedure.

DBCC ERRORLOG

EXTENTINFO(dbname, tablename, indid)

Lists extent information for all the extents belonging to an object. Dbname is the name of the database, tablename is the name of the table, and indid is the index ID of the index to list.

DBCC EXTENTINFO(''pubs'',''titles'',1)

FLUSHPROCINDB(dbid)

Forces a recompile of all the stored procedures in a database. Dbid is the database ID of the target database. This is handy when you''ve changed an option in the database that would materially affect the queries generated for its stored procedures. Sp_dboption, for example, uses DBCC FLUSHPROCINDB to ensure that changes to compile-time options are accommodated by a database''s stored procedures.

DECLARE @dbid int
SET @dbid=DB_ID(''pubs'')
DBCC FLUSHPROCINDB(@dbid)

IND(dbid, objid[,indid])

Lists system-level index information for the specified object.

DECLARE @dbid int, @objid int
SELECT @dbid=DB_ID(''pubs''),
@objid=OBJECT_ID(''pubs..authors'')
DBCC IND(@dbid,@objid, 1)

INVALIDATE_TEXTPTR(@TextPtrVal)

Invalidates the specified in-row text pointer in the transaction. If @TextPtrVal is NULL, invalidates all in-row text pointers in the current transaction. Called by sp_invalidate_textptr.

CREATE TABLE #testtxt (c1 int, c2 text)
EXEC tempdb..sp_tableoption ''#testtxt'',
''text in row'', ''on''
INSERT #testtxt VALUES (''1'',''Text lives
here'')
BEGIN TRAN
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(c2)
FROM #testtxt
READTEXT #testtxt.c2 @ptr 0 5
DBCC INVALIDATE_TEXTPTR(@ptr)
READTEXT #testtxt.c2 @ptr 0 5 -- Fails
COMMIT TRAN

LOCKOBJECTSCHEMA (objname)

Blocks schema changes by other connections until the caller commits the current transaction. It also increments the schema_ver column in sysobjects. This command has no effect if executed outside a transaction.

USE pubs
BEGIN TRAN
DBCC LOCKOBJECTSCHEMA(''titleauthor'')

LOG(dbid)

Displays log record information from the current database''s transaction log. You can use INSERT..EXEC() to trap this output in a table for further processing.

CREATE TABLE #logrecs
(CurrentLSN varchar(30),
Operation varchar(20),
Context varchar(20),
TransactionID varchar(20))
INSERT #logrecs
EXEC(''DBCC LOG(''''pubs'''')'')

MEMORYSTATUS

Provides detailed info about SQL Server memory use.

DBCC MEMORYSTATUS

NO_TEXTPTR(@TabId, @InlineSize)

Marks a table as not supporting text pointers (16-byte pointers to text pages), thus allowing in-row text. @TabId is the object ID of the table. @InlineSize is the number of characters (247,000) to store inline. Called by sp_tableoption.

CREATE TABLE testtxt (c1 int, c2 text)
DECLARE @TabId int
SET @TabId=OBJECT_ID(''testtxt'')
DBCC NO_TEXTPTR(@TabId,500)
INSERT testtxt VALUES (''1'',''Text lives
here'')
BEGIN TRAN
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(c2)
FROM testtxt
READTEXT testtxt.c2 @ptr 0 5
COMMIT TRAN

DBCC TRACEON(3604)
GO
DBCC PAGE(''pubs'',1,70,2)

PRTIPAGE(dbid, objid, indexid[, printopt { 0 | 1 | 2}])

Lists page information for the specified index

DECLARE @dbid int, @pagebin varchar(12),
@pageid int, @fileid int, @objid int
SELECT TOP 1 @dbid=DB_ID(''pubs''),
@objid=id, @pagebin=first
FROM pubs..sysindexes WHERE
id=OBJECT_ID(''pubs..authors'')
EXEC sp_decodepagebin @pagebin, @fileid
OUT, @pageid OUT
DBCC PRTIPAGE(@dbid, @objid, 2, @pageid)

PSS

Dumps the process status structure (PSS) for a given connection. There is one PSS for every connection on the server, including system connections. The PSS includes info such as the transaction control block, the isolation level, and a host of other useful info.

DBCC PSS

RESOURCE

Lists resource utilization information for the server.

DBCC TRACEON(3604)
DBCC resource
DBCC TRACEOFF(3604)

SETINSTANCE(object,counter,instance,val)

Sets the value of a Performance Monitor instance counter. You can use this when benchmarking query and stored procedure performance to set a user-definable counter inside Performance Monitor. In fact, this is how the sp_user_counternn procedures workthey call DBCC SETINSTANCE. Object is the name of the Performance Monitor object, instance is the name of the object''s instance to adjust, counter is the name of the performance counter to change, and val is the new value of the counter.

DBCC SETINSTANCE(''SQLServer:User
Settable'', ''Query'', ''User counter 1'', 3)

STACKDUMP

Dumps the call stacks of all active connections, including their input buffers. Part of this info ends up in the error log; the rest is placed in a file with a .DMP extension in the SQL Server log folder.

DBCC STACKDUMP

TAB(dbid,objid[,printopt { 0 | 1 | 2}}])

Lists system-level information for the specified table.

DECLARE @dbid int, @objid int
SELECT @dbid=DB_ID(''pubs''),
@objid=OBJECT_ID(''pubs..authors'')
DBCC TAB(@dbid, @objid, 2)

UPGRADEDB(dbname)

Upgrades the system objects in the specified database to the current version of the database engine.

DBCC UPGRADEDB(''oldpubs'')

@@MICROSOFTVERSION

Returns an internal tracking number used by Microsoft.

SELECT @@MICROSOFTVERSION
-----------
117441211

ENCRYPT(string)

Encrypts a string. It''s used internally by the server to encrypt Transact-SQL code stored in syscomments (when WITH ENCRYPTION is specified).

SELECT ENCRYPT(''VALET'')
------------------------------------------
0x4C0059004E00410052004400

GET_SID(username)

Returns the current NT system ID for a specified user or group name as a varbinary(85). Prefix username with \U to search for an NT user ID; prefix it with \G to search for an NT group ID. Note that this function only works within system stored procedures that have their system bit set. See the undocumented procedure sp_MS_marksystemobject for more information.

USE master
GO
IF (OBJECT_ID(''sp_get_sid'') IS NOT NULL)
DROP PROC sp_get_sid
GO
CREATE PROCEDURE sp_get_sid
@loginame sysname
AS
DECLARE @sid varbinary(85)
IF (charindex(''\ '', @loginame) = 0)
SELECT SUSER_SID(@loginame) AS ''SQL
User ID''
ELSE BEGIN
SELECT @sid=get_sid(''\ U''+@loginame, NULL)
IF @sid IS NULL
SELECT @sid=get_sid(''\ G''+
@loginame, NULL) -- Maybe it''s
a group
IF @sid IS NULL BEGIN
RAISERROR(''Couldn''''t find an ID
for the specified
loginame'',16,10)
RETURN -1
END ELSE SELECT @sid AS ''NT User
ID''
RETURN 0
END
GO
EXEC sp_MS_marksystemobject ''sp_get_sid''
EXEC sp_get_sid ''LEX_TALIONIS\ KHEN''

OBJECT_ID(..,''local'')

Although the OBJECT_ID() function itself is of course documented, its optional second parameter isn''t. Because you can pass a fully qualified object name as the first argument, OBJECT_ID() can return ID numbers for objects that reside in databases other than the current one. There may be times when you want to prevent this. For example, if you''re performing a task on an object that requires access to catalog information in the current database, you may need to ensure that the object name not only translates to a valid object ID, but that it''s also a local object. Pass ''local'' as OBJECT_ID()''s second parameter to ensure that it sees objects in the current database only.

USE pubs
SELECT OBJECT_ID(''Northwind..Orders''),
OBJECT_ID(''Northwind..Orders'',''local'')
----------- -----------
357576312   NULL

PLATFORM()

Returns an integer representing the operating system and version of SQL Server on which you''re running.

SELECT PLATFORM()
-----------
1025

PWDCOMPARE(str,pwd,oldenc)

Compares a string with an encrypted password. Str is the string to compare, pwd is the encrypted password to use, and oldenc is a 1 or 0 indicating whether old-style encryption was used to encrypt pwd. You can retrieve an encrypted password directly from the sysxlogins password column, or you can use the undocumented PWDENCRYPT() function to create one from a string.

SELECT PWDCOMPARE(''enmity'', password,
(CASE WHEN xstatus&2048=2048 THEN 1 ELSE 0
END))
FROM sysxlogins
WHERE name=''k_reapr''
-----------
1

PWDENCRYPT(str)

Encrypts a string using SQL Server''s password encryption algorithm. Stored procedures that manage SQL Server passwords use this function to encrypt user passwords. You can use the undocumented PWDCOMPARE() function to compare an unencrypted string with the return value of PWDENCRYPT().

SELECT PWDENCRYPT(''vengeance'') AS
EncryptedString,PWDCOMPARE(''vengeance'',
PWDENCRYPT(''vengeance''), 0) AS
EncryptedCompare
EncryptedString EncryptedCompare
EncryptedString EncryptedCompare
---------------- ----------------
--------         1

TSEQUAL(ts1,ts2)

Compares two timestamp or rowversion values. Returns 1 if they''re identical; raises an error if they''re not. The TSEQUAL() function has been around for years. It dates back to the days when Microsoft SQL Server was merely an OS/2 port of Sybase SQL Server. It''s not used as often any more, mainly because it''s no longer necessary. You can compare two timestamp/rowversion columns directly and decide for yourself whether to raise an error. There''s also no performance advantage to using TSEQUAL rather than a simple equality comparison. Still, it''s not documented in the Books Online, so I''m compelled to include it here.

USE tempdb
CREATE TABLE #testts
(k1 int identity,
rowversion rowversion)
DECLARE @ts1 rowversion, @ts2 rowversion
SELECT @ts1=@@DBTS, @ts2=@ts1
SELECT CASE WHEN TSEQUAL(@ts1, @ts2) THEN
''Equal'' ELSE ''Not Equal'' END
INSERT #testts DEFAULT VALUES
SET @ts2=@@DBTS
SELECT CASE WHEN TSEQUAL(@ts1, @ts2) THEN
''Equal'' ELSE ''Not Equal'' END
GO
DROP TABLE #testts
---------
Equal
Server: Msg 532, Level 16, State 2, Line
16
The timestamp (changed to
0x0000000000000093) shows that the row has
been updated by another user.

UNCOMPRESS()

Uncompresses a string.

SELECT CAST(CASE WHEN ([status] & 2 = 2)
THEN (UNCOMPRESS([ctext])) ELSE [ctext]
END AS nvarchar(4000))
FROM syscomments
WHERE ID=OBJECT_ID(''sp_helptext'')