InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 13:36:44 GMT20http://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]m.ciesiensky (1/6/2010)[/b][hr]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?[code="sql"]exec dbo.sp_msforeachtable 'select * from CBL.dbo.[?]'[/code][color="#FF0000"][size="1"]Msg 208, Level 16, State 1, Line 1Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.[/size][/color]Any help from anyone will be much appreciated.[/quote]The ? in sp_msforeachtable includes the schema already and is already quoted. In other words, remove the dbo. and the brackets and it should work. Something like:[code]exec sp_msforeachtable 'select * from ?'[/code]if you are doing it inside CBL or[code]exec sp_msforeachtable 'select * from CBL.?'[/code]if you want or need to fully qualify it.Tue, 12 Jan 2010 07:14:10 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxYou are going to just [b][i][u]love[/u][/i][/b] this one! Try it like this:[code="sql"]exec dbo.sp_msforeachtable 'select * from ?'[/code]Note the lack of brackets. Go figure.Wed, 06 Jan 2010 11:02:11 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]David.Poole (1/6/2010)[/b][hr]Probably a daft question but you are running the proc in the correct database?[/quote]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.Wed, 06 Jan 2010 10:47:23 GMTm.ciesienskyhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxProbably a daft question but you are running the proc in the correct database?Wed, 06 Jan 2010 10:30:15 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxTimothy,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?[code="sql"]exec dbo.sp_msforeachtable 'select * from CBL.dbo.[?]'[/code][color="#FF0000"][size="1"]Msg 208, Level 16, State 1, Line 1Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.[/size][/color][code="sql"]exec dbo.sp_msforeachtable 'select * from CBL.[?]'[/code][color="#FF0000"][size="1"]Msg 208, Level 16, State 1, Line 1Invalid object name 'CBL.[dbo].[ae_amap]'.[/size][/color][code="sql"]exec dbo.sp_msforeachtable 'select * from [?]'[/code][color="#FF0000"][size="1"]Msg 208, Level 16, State 1, Line 1Invalid object name '[dbo].[ae_amap]'.[/size][/color][code="sql"]exec dbo.sp_msforeachtable 'select * from [CBL].dbo.[?]'[/code][color="#FF0000"][size="1"]Msg 208, Level 16, State 1, Line 1Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.[/size][/color][code="sql"]exec dbo.sp_msforeachtable 'select * from [CBL].[dbo].[?]'[/code][color="#FF0000"][size="1"]Msg 208, Level 16, State 1, Line 1Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.[/size][/color]Any help from anyone will be much appreciated.Wed, 06 Jan 2010 10:01:39 GMTm.ciesienskyhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]atkinsd (11/30/2009)[/b][hr]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 tablesexec 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.[/quote]That is excellent. Thank you.Mon, 30 Nov 2009 23:13:38 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxThanks 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 tablesexec 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.Mon, 30 Nov 2009 09:55:13 GMTatkinsdhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxGreat job Tim, very useful stuff!Sat, 28 Nov 2009 13:12:55 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]einman33 (11/20/2009)[/b][hr]There extended so there married to languages that may not want to continue along into future releases.[/quote]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.Tue, 24 Nov 2009 09:51:43 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxi never knew this! it is interesting.Mon, 23 Nov 2009 21:00:17 GMThowardmarvelshttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]JStiney (11/20/2009)[/b][hr]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) endelse truncate table ##TmpSysprocesses[/quote]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,000Mon, 23 Nov 2009 04:34:05 GMTScott-144766http://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxThere extended so there married to languages that may not want to continue along into future releases.Fri, 20 Nov 2009 17:54:15 GMTeinman33http://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxThe point has been made that many DBAs know these procs and have used them for years.Great, so why doesn't Microsoft include them in BOL?They are stable and have survived a number of SQL Server editions and are generically useful.XP_DirTree and XP_FixedDrives are quite useful as well!Fri, 20 Nov 2009 16:31:21 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxBoo Birds are out for the posters bashing the author's article being to simplistic. We have to remember as SQL keeps stealing marketshare there are thousands of new DBA's every year who this is very valuable for. Just think of a the new DBA reading for the first time that MS can just yank away code without warning. That was a lesson for me 10 years ago. An article like this would have been a life saver.I rated this a petaflop worth of stars for its tone and importance.Fri, 20 Nov 2009 12:44:58 GMTeinman33http://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxsomeone tried to argue this point with me. I am glad that I could use this to prove him wrong.Fri, 20 Nov 2009 11:50:43 GMTsusiewatshttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxJStiney, that is an excellent procedure. Thank you.Fri, 20 Nov 2009 10:28:53 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]Steve Jones - Editor (11/20/2009)[/b][hr]I made a few changes to the formatting, so the article should read better now.[/quote]Thank you. The changes are not very noticeable in FireFox to me, but it looks much better in IE now.Fri, 20 Nov 2009 10:09:50 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxThe 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*/CREATE procedure [dbo].[sp_who1] @spidpool varchar(500) = null, @status sysname = null, @loginame sysname = null, @command sysname = null, @dbname sysname = null, @hostname sysname = null, @waittime int = null, @lastbatch datetime = null, @program sysname = null, @opentran int = null, @blocked bit = nullasset nocount ondeclare -- search argument in the where clause @select varchar(2000), @SARG_spid varchar(20), @SARG_status varchar(40), @SARG_loginame varchar(150), @SARG_command varchar(40), @SARG_dbname varchar(150), @SARG_hostname varchar(150), @SARG_waittime varchar(80), @SARG_lastbatch varchar(50), @SARG_program varchar(150), @SARG_opentran varchar(20), @SARG_blocked varchar(30), @SARG_all varchar(8000), @order_by_clause varchar(100), @header varchar(500), @where varchar(10), @total_users int, @total_runnables int, -- define maximum column length for dynamic adjustment @max_spid varchar(5), @max_status varchar(5), @max_loginame varchar(5), @max_dbname varchar(5), @max_command varchar(5), @max_hostname varchar(5), @max_memusage varchar(5), @max_physical_io varchar(5), @max_program_name varchar(5), @max_cpu varchar(5), @max_opentran varchar(5), @max_blocked varchar(5), @max_waittime varchar(5), @max_lastwaittype varchar(5), @max_waitresource varchar(5),-- this piece of T-SQL checks the validity of input spids pool @SARG_spidpool varchar(300), @single_spid varchar(20), @comma_position tinyint, @error varchar(100), @spidpoollength intset @spidpool = ltrim(rtrim(replace(@spidpool, ' ', '')))set @spidpoollength = len(@spidpool)set @SARG_spidpool = @spidpoolif @spidpool is not null -- user provided one or more spids begin if patindex('%[^0-9 ,]%', @spidpool) = 0 -- clean pool, only numeric, space and comma allowed begin while @spidpoollength > 0 begin set @comma_position = charindex(',', @spidpool) if @comma_position = 0 -- at the last spid set @comma_position = @spidpoollength+1 set @single_spid = substring(@spidpool, 1, @comma_position-1) if convert(int, @single_spid) not between 0 and 32767 begin set @error = 'spid ' + @single_spid + ' out of range. Valid spids are integers between 0 and 32767' raiserror (@error, 16, 1) return (1) end if charindex(',', @spidpool) = 0 set @spidpoollength = 0 else begin set @spidpool = substring(@spidpool, @comma_position+1, len(@spidpool)-@comma_position) set @spidpoollength = len(@spidpool) end end end else begin raiserror ('invalid character(s) in spid pool. Only numeric, space and comma allowed.', 16, 1) return (1) end end-- Make sure login name is existingif (@loginame is not null) begin if not exists (select 1 from master.dbo.syslogins with (nolock) where name = @loginame) begin raiserror(15007, -1, -1, @loginame) return (1) end end-- check if database is existingif (@dbname is not null) begin if not exists (select name from master.dbo.sysdatabases with (nolock) where name = @dbname) begin raiserror (15010, -1, -1, @dbname) return (1) end endif 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) endelse truncate table ##TmpSysprocessesinsert into ##TmpSysprocesses select spid, status, loginame, db_name(dbid), cmd, hostname, memusage, physical_io, login_time, last_batch, program_name, cpu, blocked, open_tran, waittype, waittime, lastwaittype, waitresource from master.dbo.sysprocesses with (nolock)select @max_spid = max(len(ltrim(str(spid)))), @max_status = ltrim(str(max(len(status)))), @max_loginame = ltrim(str(max(len(loginame)))), @max_dbname = ltrim(str(max(len(dbname)))), @max_command = ltrim(str(max(len(command)))), @max_hostname = ltrim(str(max(len(hostname)))), @max_memusage = max(len(ltrim(str(memusg)))), @max_physical_io = max(len(ltrim(str(phys_io)))), @max_program_name = ltrim(str(max(len(program)))), @max_cpu = max(len(ltrim(str(cpu)))), @max_opentran = max(len(ltrim(str(open_tran)))), @max_blocked = max(len(ltrim(str(blkBy)))), @max_waittime = max(len(ltrim(str(waittime)))), @max_lastwaittype = ltrim(str(max(len(lastwaittype)))), @max_waitresource = ltrim(str(max(len(waitresource)))) from ##TmpSysprocessesselect @total_users = count(spid) from ##TmpSysprocessesselect @total_runnables = count(spid) from ##TmpSysprocesses where status = 'runnable'set @header = '*** sp_who1 at ' + substring(convert(varchar(30), getdate(), 9), 1, 20) + space(1) + substring(convert(varchar(30), getdate(), 9), 25, 2) + space(5) + 'Server: ' + upper(@@servername) + space (5) + 'Total of spids: ' + ltrim(str(@total_users)) + space(5) + 'Total of runnables: ' + ltrim(str(@total_runnables)) + ' ***'+ char(10)-- When blocking occursdeclare @block_flag bitset @block_flag = 0if (@blocked is not null)begin if exists (select 1 from ##TmpSysprocesses where blkBy > 0) -- there is blocking begin set @block_flag = 1 if object_id ('tempdb..##TmpBlockingSpid') is null create table ##TmpBlockingSpid (spid int) else truncate table ##TmpBlockingSpid insert into ##TmpBlockingSpid -- save head spid of blocking chain select distinct spid from ##TmpSysprocesses where blkBy = 0 and spid in (select blkBy from ##TmpSysprocesses) print @header print '' print 'Head(s) of blocking chain is(are):' + char(13) + char(9) select distinct spid from ##TmpBlockingSpid order by spid asc print '' endendselect @SARG_spidpool = case when @spidpool is null then '' else ' and spid in (' + @SARG_spidpool + ')' end, @SARG_status = case when @status is null then '' else ' and status = ''' + @status + '''' end, @SARG_loginame = case when @loginame is null then '' else ' and loginame = ''' + @loginame + '''' end, @SARG_command = case when @command is null then '' else ' and command = ''' + @command + '''' end, @SARG_dbname = case when @dbname is null then '' else ' and dbname = ''' + @dbname + '''' end, @SARG_hostname = case when @hostname is null then '' else ' and hostname = ''' + @hostname + '''' end, @SARG_waittime = case when @waittime is null then '' else ' and waittype > 0x0000 and waittime > ' + ltrim(str(@waittime)) + '' end, @SARG_lastbatch = case when @lastbatch is null then '' else ' and last_batch >= ''' + convert(varchar(30), @lastbatch) + '''' end, @SARG_program = case when @program is null then '' else ' and program = ''' + @program + '''' end, @SARG_opentran = case when @opentran is null then '' else ' and open_tran >= ' + ltrim(str(@opentran)) + '' end, @SARG_blocked = case when @blocked is null then '' when @blocked = 0 then ' and blkBy = 0' else ' and blkBy > 0' endselect @SARG_all = @SARG_spidpool + @SARG_status + @SARG_loginame + @SARG_command + @SARG_dbname + @SARG_hostname + @SARG_waittime + @SARG_lastbatch + @SARG_program + @SARG_opentran + @SARG_blockedif len(@SARG_all) = 0 set @where = ''else set @where = ' where 'select @SARG_all = substring(@SARG_all, 6, len(@SARG_all))set @select = 'select left(spid, ' + @max_spid + ') as ''SPID'', left(status, ' + @max_status + ') AS ''status'', left(loginame, ' + @max_loginame + ') AS ''loginame'', left(dbname, ' + @max_dbname + ') AS ''dbname'', left(command, ' + @max_command + ') as ''command'', left(hostname, ' + @max_hostname + ') as ''hostname'', left(memusg, ' + @max_memusage + ') as ''memusg'', left(phys_io, ' + @max_physical_io + ') as ''phys_io'', left(substring(convert(varchar(25), login_time, 101), 1, 10) + '' '' + convert(varchar(25), login_time, 8), 20) as ''login_time'', left(substring(convert(varchar(25), last_batch, 101), 1, 10) + '' '' + convert(varchar(25), last_batch, 8), 20) as ''last_batch'', left(spid, ' + @max_spid + ') as ''SPID'', left(program, ' + @max_program_name + ') as ''program'', left(cpu, ' + @max_cpu + ') as ''cpu'', left(open_tran, ' + @max_opentran + ') as ''opentran'', left(blkBy, ' + @max_blocked + ') as ''blkBy'', left(waittime, ' + @max_waittime + ') as ''waittime'', left(lastwaittype, ' + @max_lastwaittype + ') as ''lastwaittype'', left(waitresource, ' + @max_waitresource + ') as ''waitresource'', left(spid, ' + @max_spid + ') as ''SPID'' from ##TmpSysprocesses ' set @order_by_clause = ' order by dbname asc, loginame asc, status asc, command asc'print ''if @block_flag <> 1 print @headerexec (@select + @where + @SARG_all + @order_by_clause)return (0)Fri, 20 Nov 2009 08:39:30 GMTJStineyhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]mmendoza (5/9/2008)[/b][hr][quote][b]Steve Eckhart (5/9/2008)[/b][hr]Several of the examples were unreadable, displaying only a horizontal scroll bar. What might have been the example code was in a window which appeared to be 1 or 2 pixels tall.[/quote]I was able to work around this glitch by printing the article to PDF. The code listings are printed inline with the article.I agree with Steve that this UI bug should be fixed.[/quote]This is what helped me to fix the problem also!Fri, 20 Nov 2009 07:39:46 GMTjamesharrisburghttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxI made a few changes to the formatting, so the article should read better now.Fri, 20 Nov 2009 06:45:26 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxGood job Timothy. I've heard of most of these, but a nice reminder of what they do and how they work. Easy to forget them if you don't work with them regularly.I'm sure lots of people have never heard of these commandsFri, 20 Nov 2009 06:42:05 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxTim, it was definitely of interest to me, as although I've heard of a couple of these, I've not seen listed exactly what they are/what they do, and as with anything else, wouldn't know to Google it (Bing it?) unless I knew what I was looking for.Thanks for the article!JonFri, 20 Nov 2009 06:39:22 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxwhere can we get the list of undocumented stored procedures?can you collect and publish here?Fri, 20 Nov 2009 06:29:10 GMTAyyappan Thangarajhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]timothyawiseman (5/9/2008)[/b][hr]My primary motivation in writing this article was for my own team, who I found out in conversation was not aware of all of these. I went looking for a list that I could give to them, but I never found a single article that I was happy with for that purpose. Many were focused on SQL Server 2000, and there were some changes between 2000 and 2005. Others were too comprehensive and would have required my people to wade through lists of procedures which dealt primarily with replication which we do not currently use. Others only dealt with one of them instead of providing a complete list I wanted.In the end I spent some time on a Saturday writing a list specifically for them that I thought would be useful to them. Then I decided there might be others who would find my particular list useful so I cleaned it up and submitted. Hopefully it will be of interest to others on this site.[/quote]Good call! A great reason to write an article, and I applaud your altruism.Fri, 20 Nov 2009 01:40:41 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]Charles Kincaid (5/9/2008)[/b][hr]I just keep running the delete thing multiple times. I then TRUNCATE the log :hehe: Of course if I want a realy clean small empty I just run my create scripts.[/quote]You truncate the log? Do you mean that you backup the log, switch to the SIMPLE recovery model, run a CHECKPOINT, switch back to FULL recovery, and immediately take a full database backup? ;-)Fri, 20 Nov 2009 01:38:38 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]srienstr (5/9/2008)[/b][hr]One comment:There's generally no need to do a delete without criteria, as truncate will perform better with less logging:[code]exec dbo.sp_msforeachtable 'truncate table test.dbo.[?]'[/code]That said, I'm sure it depends and there's a situation where the delete will be preferred for some reason, I just can't think of one in anything I've done.[/quote]Tables referenced by a foreign key (as Jeff mentioned)Tables referenced by an indexed viewTables published for transactional or merge replicationTRUNCATE TABLE does not fire triggers (desirable sometimes, other times not so much).Also, TRUNCATE TABLE requires the fairly heavy-duty ALTER TABLE permission. This requirement is often unpopular with DBAs who like to keep granted permissions to a minimum. It is particularly inconvenient in stored procedures.PaulFri, 20 Nov 2009 01:35:26 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]Anatol Romanov-404520 (5/9/2008)[/b][hr]All extended stored procedures mentioned in the article are well known to most of the people working with SQL Server for some years. They have been discussed many times in SQL server books and on popular SQL Server websites. Google search on "undocumented sql server stored procedures" returned 89,000 web pages. xp_FileExists for example is discussed at sqlservercentral, developersdex, databasejournal, sqlteam, dbforums, etc. The article lacks novelty but may be useful for some community members. That's why I rated it as 3-star.[/quote]I rated it 4 stars. Not that anyone should care about your or my ratings at all. I think Timothy did an excellent job with it. As Jeff mentions, just because you have heard of some of these things, doesn't mean everyone (or even most people) have. I look forward to reviewing your first article Mr Romanov :-DFri, 20 Nov 2009 01:29:34 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]rnjohnson10 (5/8/2008)[/b][hr]Are you kidding me? If something is hanging up the server, the GUI will take minutes or longer to show you what's happening. Using sp_who2 is generally pretty fast even if the server is getting creamed by something.[/quote]Um, so who mentioned anything about a hung server? We have the DAC for that. 2005 Activity Monitor was pretty good, all things considered. Try 2008 Activity Monitor (which really does suck!) and prepare to miss the 2005 version.[quote][b]rnjohnson10 (5/8/2008)[/b][hr]I have a custom sp_who3 script which simply modifies the sp_who2 script to only show active processes. That way you can quickly see the active processes when something is slowing the server.[/quote]Well done you! MVP Adam Machanic also has a very fine piece of work available for download called [url=http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx]sp_whoisactive[/url]. I recommend it - even if you just look at some of the awesome techniques Adam uses within the procedure.[quote][b]rnjohnson10 (5/8/2008)[/b][hr]I find the GUI to be worthless.[/quote]Harsh.Fri, 20 Nov 2009 01:25:49 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxOK. On that same thought here is another reminder. Always test your code all over again in a test environment before rolling out an upgrade, a service pack, or even a hotfix. This is especially true if you are doing ticks and special ops like using undocumented features.Mon, 12 May 2008 08:48:49 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxThis is the same thing that I just went through... I thought for sure that my latest article was on something very obvious. It turns out that it's not so obvious to a large part of the community and several of the "old dogs" appreciated the "reminder". Further, there are a lot of "newbies" that enter the deep blue realm of SQL server every day. These type of articles are perfect for them, as well.Well done Timothy! Keep them coming!Sat, 10 May 2008 07:32:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxThe article has a good list of stored procedures. I was not aware of some of the procedures.:)Sat, 10 May 2008 01:58:28 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]James Rochez (5/9/2008)[/b][hr][quote]XP_FileExistThe usage is:EXEC xp_fileexist <filename> [, <file_exists INT> OUTPUT]If executed without providing an output variable, it will display 3 columns indicating whether the table passed exists, whether it is a directory, and if the parent directory exists.[/quote]Do you mean "indicating whether the [b]file[/b] passed exists" ?[/quote]Yes. Sorry I missed that one when I edited.Fri, 09 May 2008 18:04:14 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]Anatol Romanov (5/9/2008)[/b][hr]All extended stored procedures mentioned in the article are well known to most of the people working with SQL Server for some years. They have been discussed many times in SQL server books and on popular SQL Server websites. Google search on "undocumented sql server stored procedures" returned 89,000 web pages. xp_FileExists for example is discussed at sqlservercentral, developersdex, databasejournal, sqlteam, dbforums, etc. The article lacks novelty but may be useful for some community members. That's why I rated it as 3-star.[/quote]You are absolutely right that these are all fairly well known amoungst certain communities of users, and I am certain that sp_who2 in particular is well known by most people who read this site. I certainly did not expect it to be breaking much new ground. Alexander Chigrik for instance has an excellent article on this same site on SQL Server 2000 undocumented procedures.My primary motivation in writing this article was for my own team, who I found out in conversation was not aware of all of these. I went looking for a list that I could give to them, but I never found a single article that I was happy with for that purpose. Many were focused on SQL Server 2000, and there were some changes between 2000 and 2005. Others were too comprehensive and would have required my people to wade through lists of procedures which dealt primarily with replication which we do not currently use. Others only dealt with one of them instead of providing a complete list I wanted.In the end I spent some time on a Saturday writing a list specifically for them that I thought would be useful to them. Then I decided there might be others who would find my particular list useful so I cleaned it up and submitted. Hopefully it will be of interest to others on this site.Fri, 09 May 2008 18:02:13 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]srienstr (5/9/2008)[/b][hr][quote][b]Jeff Moden (5/9/2008)[/b][hr]One reason might be, you can't TRUNCATE if FK is present... heh, of course, that also means you need to delete in the correct table order.[/quote]Would using sp_MSdependencies provide a way for managing that? (I don't see a way to handle it without iterative processing, though it can at least be done using WHILE rather than a cursor)[/quote]I just keep running the delete thing multiple times. I then TRUNCATE the log :hehe: Of course if I want a realy clean small empty I just run my create scripts.Fri, 09 May 2008 12:58:45 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]Kevin Mao (5/9/2008)[/b][hr]Wow, that's great. For the first extended store procedure xp_fileexist which exists in Sql 2000. Others are new in SQL 2005. [/quote]Are you sure? I'm able to use sp_MSforeachdb and sp_MSforeachtable on my SQL Server 2000 instance.[edit] I also see sp_MSdependencies, though I haven't used it.Fri, 09 May 2008 12:38:54 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspxWow, that's great. For the first extended store procedure xp_fileexist which exists in Sql 2000. Others are new in SQL 2005. Thanks a lot. :)Fri, 09 May 2008 12:30:57 GMTKevin Mao-243787http://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]srienstr (5/9/2008)[/b][hr]Would using sp_MSdependencies provide a way for managing that? (I don't see a way to handle it without iterative processing, though it can at least be done using WHILE rather than a cursor)[/quote]Heh... I suppose... haven't used sp_MSdependencies in a long time. If you do use sp_MSdependencies, then your actually using a loop anyway. :DFri, 09 May 2008 09:50:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]Jeff Moden (5/9/2008)[/b][hr]One reason might be, you can't TRUNCATE if FK is present... heh, of course, that also means you need to delete in the correct table order.[/quote]Would using sp_MSdependencies provide a way for managing that? (I don't see a way to handle it without iterative processing, though it can at least be done using WHILE rather than a cursor)Fri, 09 May 2008 09:35:05 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote]XP_FileExistThe usage is:EXEC xp_fileexist <filename> [, <file_exists INT> OUTPUT]If executed without providing an output variable, it will display 3 columns indicating whether the table passed exists, whether it is a directory, and if the parent directory exists.[/quote]Do you mean "indicating whether the [b]file[/b] passed exists" ?Fri, 09 May 2008 09:26:34 GMTJames Rochezhttp://www.sqlservercentral.com/Forums/Topic497570-1152-1.aspx[quote][b]srienstr (5/9/2008)[/b][hr]One comment:[code]exec dbo.sp_msforeachtable 'delete test.dbo.[?]'[/code]There's generally no need to do a delete without criteria, as truncate will perform better with less logging:[code]exec dbo.sp_msforeachtable 'truncate table test.dbo.[?]'[/code]That said, I'm sure it depends and there's a situation where the delete will be preferred for some reason, I just can't think of one in anything I've done.[/quote]One reason might be, you can't TRUNCATE if FK is present... heh, of course, that also means you need to delete in the correct table order.Fri, 09 May 2008 09:16:29 GMTJeff Moden