SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Undocumented Extended and Stored Procedures


Undocumented Extended and Stored Procedures

Author
Message
einman33
einman33
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 512
There extended so there married to languages that may not want to continue along into future releases.
Scott-144766
Scott-144766
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1526 Visits: 1102
JStiney (11/20/2009)
The following is a much more flexible and much more useful version of sp_who/2 that I have been using for a number of years.


/* Author: Richard Ding
** Creation Date: 10/10/2004
** Version: 1.0.0
*/


if object_id('tempdb..##TmpSysprocesses') is null
begin
create table ##TmpSysprocesses -- hold critical info and minimize performance hit on sys
( spid smallint,status nchar(30),loginame nchar(128),dbname nchar(128),command nchar(16),hostname nchar(128),memusg int,
phys_io int,login_time datetime,last_batch datetime,program nchar(128),cpu int,blkBy smallint,open_tran smallint,waittype binary(2),
waittime int,lastwaittype nchar(32),waitresource nchar(512) )
create clustered index clust on ##TmpSysprocesses (spid)
-- create nonclustered index nclust on ##TmpSysprocesses (status, loginame, dbname,
-- command, hostname, last_batch, waittime, open_tran)
end
else
truncate table ##TmpSysprocesses




Maybe we need to reboot our servers more often, but I had to set ##TmpSysprocesses.waittime to a bigint to get this code to run as there were several entries over 13,000,000,000

--
Scott
howardmarvels
howardmarvels
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
i never knew this! it is interesting.

Howard
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 920
einman33 (11/20/2009)
There extended so there married to languages that may not want to continue along into future releases.


Some extended procedures are documented and those are very likely to remain supported for a very long time.

The Undocumented stored procedures are distinct not supported; however, many of them are highly useful and have remained available for several successive releases now.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14876 Visits: 9518
Great job Tim, very useful stuff!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
atkinsd
atkinsd
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 24
Thanks for the article. I've used a number of these stored procedures before but a couple were new to me.

One interesting point that you may or may not know about sp_MSforeachtable is that you can specify a where condition for it to limit the number of tables you operate against.

For instance, if all of your audit tables ended with the word AUDIT you could truncate only that group by using the following command.

--truncate all AUDIT tables
exec sp_MSforeachtable @command1 = "truncate table ? ", @whereand = "and o.name like '%AUDIT' "

It is important to specify the 'o' in 'o.name' as that is the underlying alias for the sys.objects table in the stored procedure.
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 920
atkinsd (11/30/2009)
Thanks for the article. I've used a number of these stored procedures before but a couple were new to me.

One interesting point that you may or may not know about sp_MSforeachtable is that you can specify a where condition for it to limit the number of tables you operate against.

For instance, if all of your audit tables ended with the word AUDIT you could truncate only that group by using the following command.

--truncate all AUDIT tables
exec sp_MSforeachtable @command1 = "truncate table ? ", @whereand = "and o.name like '%AUDIT' "

It is important to specify the 'o' in 'o.name' as that is the underlying alias for the sys.objects table in the stored procedure.


That is excellent. Thank you.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
m.ciesiensky
m.ciesiensky
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 45
Timothy,
when attempting to run sp_msforeachtable in each of the variations below, I get these errors. I have tried running from both the master and CBL databases, with all same results.
Any Ideas?

exec dbo.sp_msforeachtable 'select * from CBL.dbo.[?]'


Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.


exec dbo.sp_msforeachtable 'select * from CBL.[?]'


Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.[dbo].[ae_amap]'.


exec dbo.sp_msforeachtable 'select * from [?]'


Msg 208, Level 16, State 1, Line 1
Invalid object name '[dbo].[ae_amap]'.


exec dbo.sp_msforeachtable 'select * from [CBL].dbo.[?]'


Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.


exec dbo.sp_msforeachtable 'select * from [CBL].[dbo].[?]'


Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.



Any help from anyone will be much appreciated.
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7683 Visits: 3287
Probably a daft question but you are running the proc in the correct database?

LinkedIn Profile

Newbie on www.simple-talk.com
m.ciesiensky
m.ciesiensky
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 45
David.Poole (1/6/2010)
Probably a daft question but you are running the proc in the correct database?


I have tried running from both the master and CBL databases, with all same results. (edited original post to reflect same)

Actually, when I run from master, it returns tables from another database, not the specified "CBL" database.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search