LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
1
Guests Online:
103

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

SQL Server Programming Hacks

Microsoft SQL Server
Please wait...

SQL Server Programming Hacks

Postby SQLDenis on Fri Nov 02, 2007 3:24 pm

This is a collection of SQL hacks available on the Wiki, right now we have 8 sections and between 70 and 80 hacks. Ideally we will have more hacks and we will also have a SQL admin hacks page in the future.

Sections
* 1 NULLS
* 2 Dates
* 3 Sorting, Limiting Ranking, Transposing and Pivoting
* 4 Handy tricks
* 5 Pitfalls
* 6 Query Optimization
* 7 Undocumented but handy
* 8 Usefull Admin stuff For The Developer


Below are direct links to all the hacks on the wiki, you can also get a list of all the hacks on the wiki itself here: SQL Server Programming Hacks


NULLS

Dates

Sorting, Limiting,Ranking, Transposing and Pivoting


Handy tricks


Pitfalls

Query Optimization

Undocumented but handy


Usefull Admin stuff For The Developer
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: SQL Server Programming Hacks

Postby SQLSister on Thu Jun 05, 2008 2:24 pm

Nice Dennis, You guys have been busy haven't you?
SQLSister
LTD Senior Moderator
LTD Senior Moderator
LTD Bronze - Rating: 189LTD Bronze - Rating: 189LTD Bronze - Rating: 189LTD Bronze - Rating: 189
 
Posts: 704
Joined: Wed Jun 04, 2008 10:43 pm
Location: Virginia Beach
Unrated

Re: SQL Server Programming Hacks

Postby chrissie1 on Thu Jun 05, 2008 2:31 pm

Nah, not really ;)
pink fuzzy slippers
User avatar
chrissie1
Senior Guru
Senior Guru
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135
 
Posts: 9475
Joined: Wed Oct 10, 2007 7:18 pm
Location: Belgium
Unrated

Re: SQL Server Programming Hacks

Postby hakus on Mon Jun 30, 2008 1:31 am

cool collection ... more more more
I recommend krakow school of english: learn polish krakow
hakus
Newbie
Newbie
 
Posts: 1
Joined: Mon Jun 30, 2008 1:26 am
Unrated

Re: SQL Server Programming Hacks

Postby Remou on Mon Jun 30, 2008 7:55 pm

It is a great achievement.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: SQL Server Programming Hacks

Postby SQLDenis on Mon Jun 30, 2008 8:00 pm

We had 80 hacks when we started and now we have 84. I have been concentrating on the SQL Server Admin Hacks lately
Right now we have 24 admin 'hacks' and these are not categorized yet
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: SQL Server Programming Hacks

Postby dodo666 on Tue Aug 26, 2008 9:00 am

Gr8 stuff guyz, :thumright:

Thnx.
User avatar
dodo666
Newbie
Newbie
 
Posts: 1
Joined: Thu Jun 26, 2008 12:31 pm
Location: Croatia
Unrated

Re: SQL Server Programming Hacks

Postby Emtucifor on Tue Aug 26, 2008 10:10 pm

Do you want to put "Date Range WHERE Clause Simplification" under the dates section?
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: SQL Server Programming Hacks

Postby SQLDenis on Tue Aug 26, 2008 10:42 pm

Will do (l8er 2nite)
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server Programming Hacks

Postby SQLDenis on Wed Aug 27, 2008 2:09 pm

I moved it
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server Programming Hacks

Postby JeffModen on Sun Oct 11, 2009 3:44 am

Since it's a Wiki, can anyone add to it? If so, how to add to existing Wiki posts and how to create a new one, please?
JeffModen
Apprentice
Apprentice
LTD Bronze - Rating: 12
 
Posts: 45
Joined: Fri Oct 09, 2009 5:54 am
Unrated

Re: SQL Server Programming Hacks

Postby JeffModen on Sun Oct 11, 2009 5:41 am

To give an example of why I wanted to get in on the Wiki, let's look at the undocumented hack called xp_DIRTREE. The Wiki has code in it like this...
  1. EXEC master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL'


If you run something like that, you get a listing of directories and all subdirectories along with what the depth of the directory is. While that's nice, there are other uses for xp_DIRTREE if you understand that there's not just one operand for DIR, but 3

The first operand you've already seen and guessed. It's the "base" directory that you want to work down from.

The second operand is numeric. 0 (the default when not included), says to list all subdirectories. Any other number will be the limit to the depth of subdirectories found. If you GOOGLE xp_DIRTREE, you can find many articles about this second operand.

Not so true with the third operand. I think there might only be two articles on all of GOOGLE that describes it. 0 (the default value when not included), says to list only directories. Any non-zero value tells it to also list file names along with the directories and an extra column that shows up called "file"... if that column has a 0 in it, its a directory. If that column has a 1 in it, it's a FILE!

Try running this and see...
  1. EXEC master..xp_dirtree 'C:\Program Files',2,1


I use the 3 operand version in my BULK INSERT ETL code all the time to fill a file with file names. xp_DirTree is available in 2k, 2k5, and 2k8. Dunno about 2010.
  1. CREATE TABLE #MyDir
  2.         (
  3.         RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  4.         Name   VARCHAR(256),
  5.         DEPTH  SMALLINT,
  6.         IsFile TINYINT
  7.         )
  8.  
  9.  INSERT INTO #MyDir
  10.         (Name, DEPTH, IsFile)
  11.    EXEC Master..xp_DirTree '<somedirectoryhere>',1,1
  12.  
  13.  SELECT * FROM #MyDir WHERE IsFile = 1
JeffModen
Apprentice
Apprentice
LTD Bronze - Rating: 12
 
Posts: 45
Joined: Fri Oct 09, 2009 5:54 am
Unrated

Re: SQL Server Programming Hacks

Postby chrissie1 on Sun Oct 11, 2009 7:58 am

JeffModen wrote:Since it's a Wiki, can anyone add to it? If so, how to add to existing Wiki posts and how to create a new one, please?


Editing is simple, you just go to the article and then click on the edit link in the sidebarmenu.

Adding an article is also simple (most of the time). YOu just go to the desired category and then fill in some title in the create article in this category textbox.
pink fuzzy slippers
User avatar
chrissie1
Senior Guru
Senior Guru
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135
 
Posts: 9475
Joined: Wed Oct 10, 2007 7:18 pm
Location: Belgium
Unrated

Re: SQL Server Programming Hacks

Postby Naomi on Tue Oct 13, 2009 4:06 am

BTW, thanks to both you guys I finally read it - good read. Otherwise I was just skiping this thread...
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: SQL Server Programming Hacks

Postby onpnt on Mon Jan 04, 2010 3:04 am

Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI

Re: SQL Server Programming Hacks

Postby Naomi on Thu May 06, 2010 12:42 am

I don't see a topic that returns records count of each table in every database.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: SQL Server Programming Hacks

Postby SQLDenis on Mon May 10, 2010 8:43 pm

Naomi wrote:I don't see a topic that returns records count of each table in every database.


Why would you want to do that?

just run the code here http://wiki.lessthandot.com/index.php/Sp_MSForEachtable in every DB

note that you can't combine Sp_MSForEachDB and Sp_MSForEachtable since they both use the same internal proc sp_MSforeach_worker and you will get an error, here is the code for both Sp_MSForEachDB and Sp_MSForEachtable


  1. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  2.  
  3. /*
  4.  * The following table definition will be created by SQLDMO at start of each connection.
  5.  * We don't create it here temporarily because we need it in Exec() or upgrade won't work.
  6.  */
  7.  
  8. CREATE PROC sys.sp_MSforeachdb
  9.     @command1 NVARCHAR(2000), @replacechar NCHAR(1) = N'?', @command2 NVARCHAR(2000) = null, @command3 NVARCHAR(2000) = null,
  10.     @precommand NVARCHAR(2000) = null, @postcommand NVARCHAR(2000) = null
  11. AS
  12.     SET deadlock_priority low
  13.    
  14.     /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
  15.     /* @precommand and @postcommand may be used to force a single result set via a temp table. */
  16.  
  17.     /* Preprocessor won't replace within quotes so have to use str(). */
  18.     DECLARE @inaccessible NVARCHAR(12), @invalidlogin NVARCHAR(12), @dbinaccessible NVARCHAR(12)
  19.     SELECT @inaccessible = LTRIM(STR(CONVERT(INT, 0x03e0), 11))
  20.     SELECT @invalidlogin = LTRIM(STR(CONVERT(INT, 0x40000000), 11))
  21.     SELECT @dbinaccessible = N'0x80000000'      /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
  22.  
  23.     IF (@precommand IS not null)
  24.         EXEC(@precommand)
  25.  
  26.     DECLARE @origdb NVARCHAR(128)
  27.     SELECT @origdb = DB_NAME()
  28.  
  29.     /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
  30.    /* Create the select */
  31.     EXEC(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
  32.             N' where (d.status & ' + @inaccessible + N' = 0)' +
  33.             N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' )
  34.  
  35.     DECLARE @retval INT
  36.     SELECT @retval = @@ERROR
  37.     IF (@retval = 0)
  38.         EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
  39.  
  40.     IF (@retval = 0 and @postcommand IS not null)
  41.         EXEC(@postcommand)
  42.  
  43.    DECLARE @tempdb NVARCHAR(258)
  44.    SELECT @tempdb = REPLACE(@origdb, N']', N']]')
  45.    EXEC (N'use ' + N'[' + @tempdb + N']')
  46.  
  47.     RETURN @retval
  48.  
  49.  
  50.  
  51.  
  52.  
  53.  
  54. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  55.  
  56. CREATE PROC sys.sp_MSforeachtable
  57.     @command1 NVARCHAR(2000), @replacechar NCHAR(1) = N'?', @command2 NVARCHAR(2000) = null,
  58.    @command3 NVARCHAR(2000) = null, @whereand NVARCHAR(2000) = null,
  59.     @precommand NVARCHAR(2000) = null, @postcommand NVARCHAR(2000) = null
  60. AS
  61.     /* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */
  62.     /* @precommand and @postcommand may be used to force a single result set via a temp table. */
  63.  
  64.     /* Preprocessor won't replace within quotes so have to use str(). */
  65.     DECLARE @mscat NVARCHAR(12)
  66.     SELECT @mscat = LTRIM(STR(CONVERT(INT, 0x0002)))
  67.  
  68.     IF (@precommand IS not null)
  69.         EXEC(@precommand)
  70.  
  71.     /* Create the select */
  72.    EXEC(N'declare hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =
  73. syso.object_id '
  74.          + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
  75.          + @whereand)
  76.     DECLARE @retval INT
  77.     SELECT @retval = @@ERROR
  78.     IF (@retval = 0)
  79.         EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
  80.  
  81.     IF (@retval = 0 and @postcommand IS not null)
  82.         EXEC(@postcommand)
  83.  
  84.     RETURN @retval
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server Programming Hacks

Postby Naomi on Tue May 11, 2010 2:21 am

Was a question in one of the forums I visit. Get a list of big tables in every database.

I think I'd rather do 2 traditional loops.

SQLDenis wrote:
Naomi wrote:I don't see a topic that returns records count of each table in every database.


Why would you want to do that?

just run the code here http://wiki.lessthandot.com/index.php/Sp_MSForEachtable in every DB

note that you can't combine Sp_MSForEachDB and Sp_MSForEachtable since they both use the same internal proc sp_MSforeach_worker and you will get an error, here is the code for both Sp_MSForEachDB and Sp_MSForEachtable


  1. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  2.  
  3. /*
  4.  * The following table definition will be created by SQLDMO at start of each connection.
  5.  * We don't create it here temporarily because we need it in Exec() or upgrade won't work.
  6.  */
  7.  
  8. CREATE PROC sys.sp_MSforeachdb
  9.     @command1 NVARCHAR(2000), @replacechar NCHAR(1) = N'?', @command2 NVARCHAR(2000) = null, @command3 NVARCHAR(2000) = null,
  10.     @precommand NVARCHAR(2000) = null, @postcommand NVARCHAR(2000) = null
  11. AS
  12.     SET deadlock_priority low
  13.    
  14.     /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
  15.     /* @precommand and @postcommand may be used to force a single result set via a temp table. */
  16.  
  17.     /* Preprocessor won't replace within quotes so have to use str(). */
  18.     DECLARE @inaccessible NVARCHAR(12), @invalidlogin NVARCHAR(12), @dbinaccessible NVARCHAR(12)
  19.     SELECT @inaccessible = LTRIM(STR(CONVERT(INT, 0x03e0), 11))
  20.     SELECT @invalidlogin = LTRIM(STR(CONVERT(INT, 0x40000000), 11))
  21.     SELECT @dbinaccessible = N'0x80000000'      /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
  22.  
  23.     IF (@precommand IS not null)
  24.         EXEC(@precommand)
  25.  
  26.     DECLARE @origdb NVARCHAR(128)
  27.     SELECT @origdb = DB_NAME()
  28.  
  29.     /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
  30.    /* Create the select */
  31.     EXEC(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
  32.             N' where (d.status & ' + @inaccessible + N' = 0)' +
  33.             N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' )
  34.  
  35.     DECLARE @retval INT
  36.     SELECT @retval = @@ERROR
  37.     IF (@retval = 0)
  38.         EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
  39.  
  40.     IF (@retval = 0 and @postcommand IS not null)
  41.         EXEC(@postcommand)
  42.  
  43.    DECLARE @tempdb NVARCHAR(258)
  44.    SELECT @tempdb = REPLACE(@origdb, N']', N']]')
  45.    EXEC (N'use ' + N'[' + @tempdb + N']')
  46.  
  47.     RETURN @retval
  48.  
  49.  
  50.  
  51.  
  52.  
  53.  
  54. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  55.  
  56. CREATE PROC sys.sp_MSforeachtable
  57.     @command1 NVARCHAR(2000), @replacechar NCHAR(1) = N'?', @command2 NVARCHAR(2000) = null,
  58.    @command3 NVARCHAR(2000) = null, @whereand NVARCHAR(2000) = null,
  59.     @precommand NVARCHAR(2000) = null, @postcommand NVARCHAR(2000) = null
  60. AS
  61.     /* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */
  62.     /* @precommand and @postcommand may be used to force a single result set via a temp table. */
  63.  
  64.     /* Preprocessor won't replace within quotes so have to use str(). */
  65.     DECLARE @mscat NVARCHAR(12)
  66.     SELECT @mscat = LTRIM(STR(CONVERT(INT, 0x0002)))
  67.  
  68.     IF (@precommand IS not null)
  69.         EXEC(@precommand)
  70.  
  71.     /* Create the select */
  72.    EXEC(N'declare hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =
  73. syso.object_id '
  74.          + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
  75.          + @whereand)
  76.     DECLARE @retval INT
  77.     SELECT @retval = @@ERROR
  78.     IF (@retval = 0)
  79.         EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
  80.  
  81.     IF (@retval = 0 and @postcommand IS not null)
  82.         EXEC(@postcommand)
  83.  
  84.     RETURN @retval
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: SQL Server Programming Hacks

Postby Naomi on Thu Jul 01, 2010 7:48 pm

I've added a quick new tip of renaming a column in multiple tables

http://wiki.lessthandot.com/index.php/R ... one_script
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: SQL Server Programming Hacks

Postby Fredi on Fri Jul 29, 2016 1:16 am

Nice work, cool
Fredi
Newbie
Newbie
 
Posts: 1
Joined: Fri Jul 29, 2016 1:15 am
Unrated