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)DECLARE@FK_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



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


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 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

Difference between abstract classes and interfaces

Very basic question but important to point out. Abstract classes can have concrete methods while interfaces have no methods implemented. Interfaces do not come in inheriting chain, while abstract classes come in inheriting chain. Interfaces have only pure virtual abstract method, while abstract classes may have non-abstract methods. All members are public [by default] in

Read More