SQL Server undocumented commands

Question

Post reply

SQL Server undocumented commands

Devendra Shirbad

SSC-Forever

Points: 42493
More actions
October 10, 2011 at 7:28 am

#249033

I was going through some blog post today and noticed use of some ‘undocumented commands’ in the post.
Per my understanding, if Microsoft didn’t document the commands they might have a good reason for that. Also, Microsoft doesn’t support such practices.
I would like to know community’s opinion on use of such commands.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

no...I say use the undocumented stuff whenever it's needed.

technically sp_MSForEachdb and sp_MSForEachTable are undocumented and unsupported, but widely used.

many times, MS is calling something an "undocumented feature", but added needed functionality, but without the official need to support it.

officially supporting it costs developer and support time, so it's often much easier to add a functionality, declare it unsupported, but effectively resolve an issue anyway.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Perry Whittle SSC Guru Points: 233824 More actions · Answer 2

take xp_delete_file. This proc is undocumented but yet is called by maintenance plans to prune aging text\backup files.

It seems to me the undocumented procedures are generally the ones that SQL Server uses internally and they may change at any point (CU or SP).

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 3

Perry Whittle (10/10/2011)
they may change at any point

Isn’t it a reason to worry?

Perry Whittle SSC Guru Points: 233824 More actions · Answer 4

Dev @ +91 973 913 6683 (10/10/2011)
Perry Whittle (10/10/2011)
they may change at any point
Isn’t it a reason to worry?

no, as Microsoft advise hwne using any of these procedures be prepared for them to change unexpectedly and re code your sequences that use them. And of course, you don't go applying CU's or SP's without first testing and checking what changes they bring 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 5

And of course, you don't go applying CU's or SP's without first testing and checking what changes they bring

For fresh developments it’s fine. What about the existing implementations?

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

It depends! (You should have seen that coming :-P)

For production code I tend to avoid the use of undocumented commands/features. For my own scripting I make a fair large use of undocumented commands, such as sp_MsForEachDB.

However, if the undocumented thing solves an issue, I have no problem with using it in production. I just make sure it still works when I apply SPs and CUs.

In my opinion, commands such as sp_MsForEachDB are so widely used that I highly doubt MS will ever remove them from future versions.

-- Gianluca Sartori

Perry Whittle SSC Guru Points: 233824 More actions · Answer 7

Dev @ +91 973 913 6683 (10/10/2011)
What about the existing implementations?

That's exactly what I am referring too!!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

steveb. SSC-Forever Points: 46733 More actions · Answer 8

is sp_who2 still undocumented?

as i use that all the time.

Also DBCC Page now and again..

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 9

That’s great input guys.

What I am really looking for:

‘Has anyone burnt his hands in such practice? Any major issues / concerns.’

Perry Whittle SSC Guru Points: 233824 More actions · Answer 10

Dev @ +91 973 913 6683 (10/10/2011)
Has anyone burnt his hands in such practice?

No, because we test new CUs or SPs and check what effect they have on the systems. Always check the release notes to see what has been included\removed

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 11

When I read the comments it looks like majority is in favor of it. However poll results don’t support the same.

steveb. SSC-Forever Points: 46733 More actions · Answer 12

I dont think a poll of Four people is really going to tell you anything, give it some time maybe..

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 13

give it some time maybe..

Yup… this is what I am going to do now… Leaving for the day 😀

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Gianluca Sartori (10/10/2011)
It depends! (You should have seen that coming :-P)

Though undocumented, as has been said, there is evidence of Microsoft calling their own "undocumented" code. I think it really comes down to "It Depends" as Gianluca said.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events