Archive for the ‘SQL Stored Procedure’ Category

SQL Server – DBCC Commands

DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.

I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples.

The DBCC Commands broadly falls into four categories:

  • Maintenance
  • Informational
  • Validation
  • Miscellaneous

Maintenance Commands

Performs maintenance tasks on a database, index, or filegroup.

1. CLEANTABLE – Reclaims space from the dropped variable-length columns in tables or index views.

DBCC CLEANTABLE (‘AdventureWorks’,’Person.Contact’,0)

2. DBREINDEX – Builds one or more indexes for the table in the specified database. (Will be removed in the future version, use ALTER INDEX instead)

USE AdventureWorks

DBCC DBREINDEX (‘Person.Contact’,’PK_Contact_ContactID’,80)

3. DROPCLEANBUFFERS – Removes all clean buffers from buffer pool.

DBCC DROPCLEANBUFFERS

4. FREEPROCCACHE – Removes all elements from the procedure cache

DBCC FREEPROCCACHE

5. INDEXDEFRAG – Defragments indexes of the specified table or view.

DBCC INDEXDEFRAG (‘AdventureWorks’, ‘Person.Address’, PK_Address_AddressID)

6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database

DBCC SHRINKDATABASE (‘AdventureWorks‘, 10)

7. SHRINKFILE – Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.

USE AdventureWorks;

— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks_Log, 1)

8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.

DBCC UPDATEUSAGE (AdventureWorks)

Informational Commands

Performs tasks that gather and display various types of information.

1. CONCURRENCYVIOLATION – is maintained for backward compatibility. It runs but returns no data.

DBCC CONCURRENCYVIOLATION

2. INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.

DBCC INPUTBUFFER (52)

3. OPENTRAN – Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.

DBCC OPENTRAN;

4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

DBCC OUTPUTBUFFER (52)

5. PROCCACHE – Displays information in a table format about the procedure cache.

DBCC PROCCACHE

6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target on the specified table

USE AdventureWorks

DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid)

7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view.

USE AdventureWorks

DBCC SHOWCONTIG (‘HumanResources.Employee’);

8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.

DBCC SQLPERF(LOGSPACE)

9. TRACESTATUS – Displays the status of trace flags.

DBCC TRACESTATUS(-1)

10. USEROPTIONS – Returns the SET options active (set) for the current connection.

DBCC USEROPTIONS

Validation Commands

Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

1. CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKALLOC (AdventureWorks)

2. CHECKCATALOG – Checks for catalog consistency within the specified database.

DBCC CHECKCATALOG (AdventureWorks)

3. CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.

DBCC CHECKDB (AdventureWorks)

5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

USE AdventureWorks

DBCC CHECKFILEGROUP

6. CHECKIDENT – Checks the current identity value for the specified table and, if it is needed, changes the identity value.

USE AdventureWorks;

DBCC CHECKIDENT (‘HumanResources.Employee’)

7. CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.

USE AdventureWorks;

DBCC CHECKTABLE (‘HumanResources.Employee’)

Miscellaneous Commands

Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

1. dllname (FREE) – Unloads the specified extended stored procedure DLL from memory.

DBCC xp_sample (FREE)

2. TRACEOFF – Disables the specified trace flags.

DBCC TRACEOFF (3205)

3. HELP – Returns syntax information for the specified DBCC command.

— List all the DBCC commands

DBCC HELP (‘?’)

— Show the Syntax for a given DBCC commnad

DBCC HELP (‘checkcatalog’)

4. TRACEON – Enables the specified trace flags.

DBCC TRACEON (3205)

Good Luck!

Advertisements

SQL Server – Catch the Errors Details of Stored Procedure

Catch the Errors Details of Stored Procedure

When we write a User Defined Stored Procedure, it is quite natural that it will throw errors when we are testing it initially. Generally we can see the error generated and solve it easily. But many times the error generated are cryptic and very difficult to interpret. Debugging becomes a big headache.

The errors generated are even displayed as numbers. Now how a DBA is suppose to understand the errors by just looking at the error numbers?

What we can do is use TRY CATCH blocks in the User Defined Stored Procedure. Write the actual procedure code in TRY block and use CATCH block to deal with the error thrown by code in TRY block. Then we need to render the error and display it in more readable form.

Check out the CATCH block in USP_myProcedure below:

— The Procedure with CATCH block code.

ALTER PROCEDURE [dbo].[USP_myProcedure]

AS

BEGIN

BEGIN TRY

— Procedure actual code goes here

END TRY

BEGIN CATCH

— It will catch the error and diplay it in more readable form

DECLARE @ErrorSeverity INT,

@ErrorNumber INT,

@ErrorMessage NVARCHAR(4000),

@ErrorState INT

SET @ErrorSeverity = ERROR_SEVERITY()

SET @ErrorNumber = ERROR_NUMBER()

SET @ErrorMessage = ERROR_MESSAGE()

SET @ErrorState = ERROR_STATE()

IF @ErrorState = 0

SET @ErrorState = 1

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber)

END CATCH

END

It is good practice to including the above CATCH code in all the User Defined Stored Procedure.

Good Luck!

SQL Server – Using Variable for Column name

Using Variable for Column name

It is very straight forward to use direct column names in T-SQL statements. But what if column names are dynamically supplied to T-SQL code? Can we use variables instead of column names in our query? Yes we can.

When I first came across this problem, it took me hell lot of time to find a simple solution to it. What we need to do is build the SQL statement in a string variable with all the variable names in it. And then pass it to the EXEC command for execution.

Example:

— Customer Table contains column Acc1, Acc2, Acc3 … Acc16

— We need to display Acc[???] column for given CustID – ??? -> is value supplied dynamically

DECLARE @sqlQuery VARCHAR(1000)

DECLARE @custID VARCHAR(10)

DECLARE @acc VARCHAR(5)

— here we are assigning ‘5’ for demo.

SET @acc = ‘5’

SET @acc = ‘Acc’+@acc

SET @custID=‘C308’

— Bulid the Query into a string variable

SET @sqlQuery =

(‘SELECT (‘+QUOTENAME(@acc)+‘) FROM Customer WITH (NOLOCK)

WHERE CustID=’+@CustID+‘ AND (‘+QUOTENAME(@acc)+‘) IS NOT NULL’)

— Pass the String to EXEC for execution

EXEC (@sqlquery)

Dynamic column names are generally used by user define stored procedures that takes the input which need to be passed to the SQL statement. Below is the simple USP which demonstrates the used of variable column name.

Eaxmple:

— Table StudentAttendance has columns day1, day2, day3 … and so on

— The USP displays only the column, as per number supplied.

ALTER PROCEDURE [dbo].[USP_studentExist]

(

@AcademicYearFrom INT,

@AcademicYearTo INT,

@month INT,

@day VARCHAR(5),

@StudentID VARCHAR(11)

)

AS

BEGIN

BEGIN TRY

DECLARE @query VARCHAR (1000)

DECLARE @m VARCHAR(50),

@ayf VARCHAR(50),

@ayt VARCHAR(50)

— Converting INT value to VARCHAR values

SELECT @m=@month, @ayf=@AcademicYearFrom, @ayt=@AcademicYearTo

— Bulid the Query into a string variable

SET @query = (‘SELECT day’+@day+‘ FROM dbo.StudentAttendance

WHERE studentid = ”’+@StudentID+”’

AND month=’+@m+

AND AcademicYearFrom=’+@ayf+’

AND AcademicYearTO=’+@ayt+)

EXEC (@query)

END TRY

BEGIN CATCH

PRINT @@ERROR

END CATCH

END

Be very careful with the use of use of single quote (‘) while building a query string. A silly error while using it may take lot of time to debug.

Good Luck!

SQL Server – sp_help – View Table Structure

View the Table Structure

sp_help is a stored procedure used for reporting system information. It is extensively used for displaying the structure of a table.

We shall use Customer table for the demo:

Customer Table

Customer Table

To get detailed information about Customer table execute the following command.

sp_help Customer;

Customer Table Details

The result is seven sets of information about the Customer table, including the following:

  • Name, creation date, and owner
  • Columns
  • Identity columns
  • Row GUID columns
  • FileGroup location
  • Indexes
  • Constrains