Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Undocumented Extended and Stored Procedures Expand / Collapse
Author
Message
Posted Friday, November 20, 2009 5:54 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512
There extended so there married to languages that may not want to continue along into future releases.
Post #822799
Posted Monday, November 23, 2009 4:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, March 14, 2014 9:23 AM
Points: 762, Visits: 717
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 Doughty
Post #823186
Posted Monday, November 23, 2009 9:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 23, 2009 8:57 PM
Points: 1, Visits: 0
i never knew this! it is interesting.

Howard
Post #823606
Posted Tuesday, November 24, 2009 9:51 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #823992
Posted Saturday, November 28, 2009 1:12 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Great job Tim, very useful stuff!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #825843
Posted Monday, November 30, 2009 9:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 12, 2010 8:34 AM
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.
Post #826324
Posted Monday, November 30, 2009 11:13 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #826491
Posted Wednesday, January 06, 2010 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 04, 2010 10:46 AM
Points: 21, 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.
Post #842982
Posted Wednesday, January 06, 2010 10:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 2,866, Visits: 1,708
Probably a daft question but you are running the proc in the correct database?

LinkedIn Profile
Newbie on www.simple-talk.com
Post #843008
Posted Wednesday, January 06, 2010 10:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 04, 2010 10:46 AM
Points: 21, 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.
Post #843021
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse