SET SHOWPLAN_TEXT

Syntax: SET SHOWPLAN_TEXT { ON OFF }Remarks: The setting of SET SHOWPLAN_TEXT is set at execute or run time and not at parse time.SET SHOWPLAN_TEXT ONGOUSE pubsSELECT * FROM royschedWHERE title_id = ‘PS1372’GOSET SHOWPLAN_TEXT OFFGO

SQL Server script to rebuild all indexes for all tables and all databases

A very good and useful script for DBA DECLARE @Database VARCHAR(255)DECLARE @Table VARCHAR(255)DECLARE @cmd NVARCHAR(500)DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FORSELECT name FROM master.dbo.sysdatabasesWHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’distrbution’)ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @DatabaseWHILE @@FETCH_STATUS = 0BEGIN SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog +

Read More

Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server

A very useful script SET NOCOUNT ONDECLARE @operation VARCHAR(10)DECLARE @tableName sysnameDECLARE @schemaName sysnameSET @operation = ‘DROP’ –ENABLE, DISABLE, DROPSET @tableName = ‘SpecialOfferProduct’SET @schemaName = ‘Sales’DECLARE @cmd NVARCHAR(1000)[email protected]_NAME sysname,@FK_OBJECTID INT,@FK_DISABLED INT,@FK_NOT_FOR_REPLICATION INT,@DELETE_RULE smallint,@UPDATE_RULE smallint,@FKTABLE_NAME sysname,@FKTABLE_OWNER sysname,@PKTABLE_NAME sysname,@PKTABLE_OWNER sysname,@FKCOLUMN_NAME sysname,@PKCOLUMN_NAME sysname,@CONSTRAINT_COLID INT DECLARE cursor_fkeys CURSOR FORSELECT Fk.name,Fk.OBJECT_ID,Fk.is_disabled,Fk.is_not_for_replication,Fk.delete_referential_action,Fk.update_referential_action,OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,schema_name(Fk.schema_id) AS Fk_table_schema,TbR.name AS Pk_table_name,schema_name(TbR.schema_id) Pk_table_schemaFROM sys.foreign_keys Fk LEFT

Read More

queryplan-I

SQL Server 2000 – SET SHOWPLAN_ALL

Syntax: SET SHOWPLAN_ALL { ON OFF }The setting of SET SHOWPLAN_ALL is set at execute or run time and not at parse time. Run folowing SqlUSE pubsGOSET SHOWPLAN_ALL ONGO— First query.SELECT au_idFROM authorsWHERE au_id = ‘409-56-7008’GO— Second query.SELECT cityFROM authorsWHERE city LIKE ‘San%’GOSET SHOWPLAN_ALL OFFGO Query plan for above is as followed

1206_tablescan

Understanding SQL Server Indexing

A very good Article written by : Edgewood Solutions Engineers Problem: With so many aspects of SQL Server to cover and to write about, some of the basic principals are often overlooked. There have been several people that have asked questions about indexing along with a general overview of the differences of clustered and non

Read More

Working with Stored procedures

Stored procedure helps to make your work easy. With the help of these you just have to supply some parameters only. The Code snippet using storedprocedures describes a way to use stored procedures.

Working with Transactions

Whenever you have to update more than one table or destinations then it must have to sure that the operation done successfully because some time it has been seen that one table updated but due to some error another(s) not. To overcome this problem you have Transactions. In ADO.Net transactions are initiated by calling BeginTransaction()

Read More