Pearls: SQL Comments

When you are writing SQL code, very often you run parts in isolation by commenting out what you don't need and uncommenting portion that is to be executed.

A very simple approach of writing SQL comments coupled with SSMS(studio) shortcuts for commenting/uncommenting sections of code can help you do your unit testing efficiently.

First you need to know these SSMS short cuts:

Cntrl+K followed by Cntrl+C :

This comments ​current SQL line

Cntrl+K followed by Cntrl+U:

This uncomments the current SQL line

​​

--/* booklist section starts
SELECT * FROM BookList
--*/
--/*Book section starts
SELECT * FROM Books
--*/​

​Now all you need to do to comment Booklist section is press cntrl+K &cntrl-U on line #1 . To uncomment back the section , you'll need to comment out the line#1 by pressing cntrl+K&cntrl+C

The hidden Stored Procedures in SQL-II

Just like sp_MSforeachtable  you also have a stored procedure which runs for each DB.

It is sp_MSforeachdb

SYNTAX

EXEC @RETURN_VALUE=sp_MSforeachdb @command1,

     @replacechar,

     @command2,

     @command3,

     @precommand,

     @postcommand

where:

@RETURN_VALUE - the return value which will be set by "sp_MSforeachtable"

@command1 - first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)

@replacechar - character in the command string that will be replaced with the table name being processed (default replacechar is a "?")

@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2

@precommand - nvarchar(2000) parameter and specifies a command to be run prior to processing any table

@postcommand - nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

Some example usages-

• To check space used by each DB

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

• Change all DB owners to sa

EXEC sp_MSforeachdb @command1='use ?; exec sp_changedbowner ''sa'''

3 Simple ways to check if a table exists in DB

Many times we ought to search if a table exist in DB. At those times we typically search the object explorer and do a manual search. This is OK if we have like 1-50 tables; but for huge databases it is best to run a query and find the result.Below are 3 queries which use different methods to find the same thing

1.  Using Object_ID approach

SELECT CASE WHEN OBJECT_ID (N'books', N'U') IS NOT NULL THEN  'Yes' ELSE  'No' END AS [DoesTableExists?]

2.  Using Information_Schema approach

SELECT CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='books') THEN 'yes'  ELSE 'no'  END AS [DoesTableExists?];

3.  Using Sys.tables approach

select case when exists(select 1 from sys.tables where name like 'books' )

then 'yes' else 'no' end as [DoestableExists?]

The hidden Stored Procedures in SQL

Not all SQL stored procedures have been documented. Some of these hidden/undocumented SP are quite useful. We’ll begin this series of blog by discussing some of these, one at a time.

Today we’ll discuss about the hidden SP - sp_MSforeachtable.

This very versatile SP works on all the tables in your databases. As such it can be used to run same query/code on multiple tables in a database. It is present in the master database.

It also supports one or more commands as arguments and we can use wild card character ? in the command queries  to take care of different tables.

SYNTAX

EXEC @RETURN_VALUE=sp_MSforeachtable @command1,

     @replacechar,

     @command2,

     @command3,

     @whereand,

     @precommand,

     @postcommand

where:

@RETURN_VALUE - the return value which will be set by "sp_MSforeachtable"

@command1 - first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)

@replacechar - character in the command string that will be replaced with the table name being processed (default replacechar is a "?")

@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2

@whereand - can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)

@precommand - nvarchar(2000) parameter and specifies a command to be run prior to processing any table

@postcommand - nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

Some example usages are below-

·                                                         ·         Run DBCC check on all tables.

EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])'

·         Count rows in each table

CREATE TABLE #counts ( TABLE_NAME varchar(255), ROW_COUNT int) 

EXEC sp_MSForEachTable 

@command1=

'INSERT #counts(table_name,row_count) SELECT ''?'',COUNT(*) FROM ?'

SELECT TABLE_NAME,ROW_COUNT

FROM #counts

ORDER BY TABLE_NAME,ROW_COUNT DESC

·         Disable triggers in all tables

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'