Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Tools

Authors

SQLDenis (164)
onpnt (82)
George Mastros (31)
chrissie1 (8)
emtucifor (6)
naomi (6)
AlexCuse (6)
thirster42 (4)
riverguy (1)
ca8msm (1)
pmch22 (1)
ptheriault (1)
More...

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

Google Ads

« Beginning stages of a DR plan for SQL Server

Back to business for the SQL Server community »

comments

Identify procedures that call SQL Server undocumented procedures

by George Mastros on Nov 13, 2009 in categories Data Modelling & Design

Rate Post:

Currently 3.57/5
1
2
3
4
5
i

When you use an undocumented stored procedure, you run the risk of not being able to upgrade your database to a new version. What's worse... you could have broken functionality and not even know it. With undocumented stored procedures, Microsoft may not document when they decide to deprecate it, so you may not know about your broken functionality until it's too late.

Presented below is a hard coded list of undocumented stored procedures. By their very nature, it is hard to find documentation on undocumented procedures. Therefore, the procedures in the list below is likely to be incomplete.

How to detect this problem:

tsql

Line number Off | Hide | Select all

DECLARE @Temp TABLE(ProcedureName VARCHAR(50))
INSERT INTO @Temp VALUES('sp_MStablespace')
INSERT INTO @Temp VALUES('sp_who2')
INSERT INTO @Temp VALUES('sp_tempdbspace')
INSERT INTO @Temp VALUES('sp_MSkilldb')
INSERT INTO @Temp VALUES('sp_MSindexspace')
INSERT INTO @Temp VALUES('sp_MShelptype')
INSERT INTO @Temp VALUES('sp_MShelpindex')
INSERT INTO @Temp VALUES('sp_MShelpcolumns')
INSERT INTO @Temp VALUES('sp_MSforeachtable')
INSERT INTO @Temp VALUES('sp_MSforeachdb')
INSERT INTO @Temp VALUES('sp_fixindex')
INSERT INTO @Temp VALUES('sp_columns_rowset')
INSERT INTO @Temp VALUES('sp_MScheck_uid_owns_anything')
INSERT INTO @Temp VALUES('sp_MSgettools_path')
INSERT INTO @Temp VALUES('sp_gettypestring')
INSERT INTO @Temp VALUES('sp_MSdrop_object')
INSERT INTO @Temp VALUES('sp_MSget_qualified_name')
INSERT INTO @Temp VALUES('sp_MSgetversion')
INSERT INTO @Temp VALUES('xp_dirtree')
INSERT INTO @Temp VALUES('xp_subdirs')
INSERT INTO @Temp VALUES('xp_enum_oledb_providers')
INSERT INTO @Temp VALUES('xp_enumcodepages')
INSERT INTO @Temp VALUES('xp_enumdsn')
INSERT INTO @Temp VALUES('xp_enumerrorlogs')
INSERT INTO @Temp VALUES('xp_enumgroups')
INSERT INTO @Temp VALUES('xp_fileexist')
INSERT INTO @Temp VALUES('xp_fixeddrives')
INSERT INTO @Temp VALUES('xp_getnetname')
INSERT INTO @Temp VALUES('xp_readerrorlog')
INSERT INTO @Temp VALUES('sp_msdependencies')
INSERT INTO @Temp VALUES('xp_qv')
INSERT INTO @Temp VALUES('xp_delete_file')
INSERT INTO @Temp VALUES('sp_checknames')
INSERT INTO @Temp VALUES('sp_enumoledbdatasources')
INSERT INTO @Temp VALUES('sp_MS_marksystemobject')
INSERT INTO @Temp VALUES('sp_MSaddguidcolumn')
INSERT INTO @Temp VALUES('sp_MSaddguidindex')
INSERT INTO @Temp VALUES('sp_MSaddlogin_implicit_ntlogin')
INSERT INTO @Temp VALUES('sp_MSadduser_implicit_ntlogin')
INSERT INTO @Temp VALUES('sp_MSdbuseraccess')
INSERT INTO @Temp VALUES('sp_MSdbuserpriv')
INSERT INTO @Temp VALUES('sp_MSloginmappings')
INSERT INTO @Temp VALUES('sp_MStablekeys')
INSERT INTO @Temp VALUES('sp_MStablerefs')
INSERT INTO @Temp VALUES('sp_MSuniquetempname')
INSERT INTO @Temp VALUES('sp_MSuniqueobjectname')
INSERT INTO @Temp VALUES('sp_MSuniquecolname')
INSERT INTO @Temp VALUES('sp_MSuniquename')
INSERT INTO @Temp VALUES('sp_MSunc_to_drive')
INSERT INTO @Temp VALUES('sp_MSis_pk_col')
INSERT INTO @Temp VALUES('xp_get_MAPI_default_profile')
INSERT INTO @Temp VALUES('xp_get_MAPI_profiles')
INSERT INTO @Temp VALUES('xp_regdeletekey')
INSERT INTO @Temp VALUES('xp_regdeletevalue')
INSERT INTO @Temp VALUES('xp_regread')
INSERT INTO @Temp VALUES('xp_regenumvalues')
INSERT INTO @Temp VALUES('xp_regaddmultistring')
INSERT INTO @Temp VALUES('xp_regremovemultistring')
INSERT INTO @Temp VALUES('xp_regwrite')
INSERT INTO @Temp VALUES('xp_varbintohexstr')
INSERT INTO @Temp VALUES('sp_MSguidtostr')
SELECT DISTINCT Name
FROM sysobjects
INNER Join @Temp T
ON Object_Definition(id) Like '%' + T.ProcedureName + '%'
WHERE XType = 'P'
And OBJECTPROPERTY(ID, N'IsMSShipped') = 0
ORDER BY Name

How to correct it: Rewrite your functionality so that is does not rely upon undocumented procedures.

Level of severity: moderate to high

Level of difficulty: moderate to high. Undocumented stored procedures are often used because it's easy. Replacing it is usually NOT easy.

About the Author

George has been developing software professionally for 19 years, first for the department of defense, and then for various other companies. In 1998, George started his software company, Orbit Software, specializing in School Bus Transportation software. His specialty is refining SQL Server queries to deliver optimal performance.

Social Sitings

Article Views: 359 views

Rate Post:

Currently 3.57/5
1
2
3
4
5
i

Comments and Feedback

13 comments

Comment from: Aaron Bertrand [Visitor] · http://sqlblog.com/blogs/aaron_bertrand/

Here are more:

xp_qv
xp_delete_file

I'd also be curious to see which MS-shipped stored procedures use undocumented stuff, so I would take out the OBJECTPROPERTY clause in that case... not that *I* need to worry about them breaking, since the procs should be fixed by MS during an upgrade if the procs they call have breaking changes. But just for the curiosity factor.

11/13/09 @ 07:45

Comment from: George Mastros [Member]

Thanks Aaron. I added those 2 undocumented procedures to the list.

Does anyone else know of other undocumented procedures that should be included in this list?

11/13/09 @ 07:54