Replicate Function in SQL Server 2012

This is a New function in SQL Server 2012

It repeats the string/character expression N number of times specified in the function.

Example :

DECLARE @FirstString nVARCHAR(MAX)
SET @FirstString = REPLICATE('A',11)
Select @FirstString
SELECT LEN(@FirstString) LenFirstString;

Result
AAAAAAAAAAA

LenFirstString
11

New Conversion Function In SQL Server 2012

Conversion Function

1- Parse()  function (can convert any string value to Numeric or Date/Time format.)

–Example1: Converting String to INT
SELECT PARSE(‘100.000’ AS INT) AS ValueInt
 select parse()

 –Example2: Converting String to Date/Time
SELECT PARSE(‘July 30, 2011’ AS DATETIME) AS ValueDT

2- Try_parse() Function

The TRY_PARSE() function can convert any string value to Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

Example1: Converting String to INT

— No error
 SELECT PARSE(‘100.000’ AS INT) AS ValueInt;
 SELECT TRY_PARSE(‘100.000’ AS INT) AS ValueInt;
 — Error
 SELECT PARSE(‘sa300.000’ AS INT) AS ValueInt;
 SELECT TRY_PARSE(‘mostafa1985.10’ AS INT) AS ValueInt;

3-  TRY_CONVERT() function

This Function like Convert but convert function if you try to convert string to int it will result Error but TRY_COBVERT it will give you NULL like TRY_PARSE() Function .

—No Error
SELECT CONVERT(INT, ‘500’) AS ValueInt;
 SELECT TRY_CONVERT(INT, ‘500’) AS ValueInt;
—Error
 SELECT CONVERT(INT, ‘m100.000’) AS ValueInt;
 SELECT TRY_CONVERT(INT, ‘m100.000’) AS ValueInt;

Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

Diffrence Between pars() and Try_Pars() Function : Parse if try to use it with one or more incorrect values will throw an error However, if you use TRY_PARSE function it will not throw error but will return parse string to  the result as NULL

difference between Convert() and TRY_CONVERT() functions: Convert function try to convert the string and return the value if it can if it can’t it will return Error , However Try_Convert Function it will try to convert the string and return value if it can but if it can’t it will return NULL.

Date and Time Functions In SQL Server 2012

Date and Time Functions In SQL Server 2012

DATEFROMPARTS ( year, month, day)
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
EOMONTH ()  — TO SHOW CND DATE IN MONTH
 set dateformat dmy
 select DATEFROMPARTS (1985,10,01)
 SELECT DATETIME2FROMPARTS (1985,10,1,11,31,00,0,0)
  SELECT DATETIMEFROMPARTS (1985,10,1,11,31,00,100)
 SELECT DATETIMEOFFSETFROMPARTS (1985,10,1,11,33,12,0,11,0,7)
 SELECT SMALLDATETIMEFROMPARTS (2010,12,31,23,59)
 SELECT TIMEFROMPARTS (23,59,59,0,0)
select EOMONTH (‘1985/10/1’)

Logical Function in SQL Server 2012

IIF() Function

IIF () Function is great function and all developer know this function because its already in VB.NET but the new it’s will be used it in SQL

IIF () Function Syntax

IIF(the Condition , ‘1’,’2′) if the condition is true the result will return no 1 , Else the result will return no 2

Examples

SELECT IIF ( -1 < 1, ‘TRUE’, ‘FALSE’ ) AS Result;
—Example 2
DECLARE @NAME NVARCHAR(15)= ‘MOSTAFA’
SELECT IIF(LEN(@NAME) > 5 , ‘BIG NAME’ , ‘SHORT NAME’) AS LONG
—Example 3
DECLARE @VARIBALE INT =   NULL
SELECT IIF (@VARIBALE IS NULL
, ‘YES’,’NO’)
—Example 4
 CREATE TABLE EMPLOYEE
 (
 EMP_NAME NVARCHAR(50),
 CODE NVARCHAR(50)
 )
 INSERT INTO EMPLOYEE VALUES (‘MOSTAFA’,’M100′),(‘ABDEL-KAREEM’,’100′),(‘OMAR’,’O100′)
SELECT EMP_NAME,CODE,IIF(TRY_PARSE(CODE AS INT) IS NULL , ‘VALUE IS STRING’ , ‘VALUE IS NOT STRING’)
  FROM EMPLOYEE

CHOOSE() FUNCTION

Choose Function is very simple function if the index is numeric it will convert to integer , and if the index is great than the element in the list it will return NULL

Example NO 1

 SELECT CHOOSE ( 1, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_First;

 SELECT CHOOSE ( 2, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Second;

 SELECT CHOOSE ( 3, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Third;

Example NO 2 (if the index is great than the elemant in the list )

SELECT CHOOSE ( 0, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Null;

 SELECT CHOOSE ( 4, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Result_NULL;

Example NO 3 ( if the indesx is numeric it will convert to integer )

 SELECT CHOOSE ( 1.1, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_First;

 SELECT CHOOSE ( 2.9, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Second;

String Function in SQL Server 2012

CONCAT() Function

Concat Function is new function in SQL Server 2012 this Function is very Great Function becouse in 2008 when we want to concat two name + Number we must make ++and Cast the Number to String that’s very hard today in this version we will make this operation in one and easy step . Let’s See How :

SELECT CONCAT(1, 2, 3, 4) AS SingleString
SELECT CONCAT(‘One’,1, 1.1, GETDATE()) AS SingleString
SELECT CONCAT(‘One’,2,NULL) AS SingleString
SELECT CONCAT(”,”,”,”) AS SingleString
SELECT CONCAT(NULL, NULL) AS SingleString

in old SQL Server when you want to conact Mostafa + Elmasry +1985 you write

Select ‘Mostafa’ + ‘ Elmasry’ + Cast(‘ 1985’ as Nvarchar(10)) AS My_Name

But in New Version that’s very easy see :

Select Concat(‘Mostafa’,’ Elmasry’,1985)


Format() Function

SET DATEFORMAT DMY
GO
DECLARE @DAY DATETIME = ’17/09/2010′;
SELECT FORMAT ( @DAY, ‘d’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @DAY, ‘d’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @DAY, ‘d’, ‘de-DE’ ) AS DE_Result;
GO
DECLARE @Month DATETIME = ’17/09/2010′;
SELECT FORMAT ( @Month, ‘M’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @Month, ‘M’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @Month, ‘M’, ‘de-DE’ ) AS DE_Result;
GO
DECLARE @YEAR DATETIME = ’17/09/2010′;
SELECT FORMAT ( @YEAR, ‘Y’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @YEAR, ‘Y’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @YEAR, ‘Y’, ‘de-DE’ ) AS DE_Result;
GO

Day
SELECT FORMAT ( GETDATE(), ‘d’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘dd’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘ddd’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘dddd’, ‘en-US’ ) AS US_Result;

Month
SELECT FORMAT ( GETDATE(), 'm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmmm', 'en-US' ) AS US_Result;

Year
SELECT FORMAT ( GETDATE(), 'y', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yy', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yyy', 'en-US' ) AS US_Result;

Currency
DECLARE @var INT = 50
SELECT FORMAT(@var,’c’) AS Currency;
SELECT FORMAT(@var,’c1′) AS Currency;
SELECT FORMAT(@var,’c2′) AS Currency;
SELECT FORMAT(@var,’c3′) AS Currency;
GO
DECLARE @d INT = 500;
SELECT FORMAT ( @d, ‘c’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @d, ‘c’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @d, ‘c’, ‘de-DE’ ) AS DE_Result;

miscalculation format
DECLARE @var INT = 50
SELECT FORMAT(@var,’p’) AS Percentage;
SELECT FORMAT(@var,’e’) AS Scientific;
SELECT FORMAT(@var,’x’) AS Hexa;
SELECT FORMAT(@var,’x4′) AS Hexa1;

language
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘en-US’) AS English_Result;
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘AR’) AS ARABIC_Result;
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘gu’) AS Gujarati_Result;

Avoiding deadlocks by using READ_COMMITED_SNAPSHOT isolation level

Programmer 1: There's a deadlock happening when this procedure is run

Programmer 2: Did you try using NOLOCK hint in SELECT statements? It should prevent most deadlocks from happening

...

How many times have we heard the above type of conversation in our daily life?

One of most common and quick solutions provided for handling deadlock scenarios involves the use of NOLOCK table hint. NOLOCK hint actually ignores the normal locks that are placed on the object and held by a transaction and it allows the current query to complete without waiting for the first transaction to finish and release the locks. The problem with this approach is that it often returns unstable data like data which is uncommited and in use by the current transaction and you may end with dirty reads. So unless you're 100% sure that data you're dealing with is non volatile NOLOCK should not be used.

Fortunately SQL 2005 has brought for us a new isolation level calledREAD_COMMITTED_SNAPSHOT which we can leverage upon for dealing with similar situations. READ_COMMITTED_SNAPSHOT isolation level works based on row versioning concept and hence improves read concurrency avoiding deadlocks on most cases

 READ_COMMITTED_SNAPSHOT works closely like the READ_COMMITTED isolation level which is the default mode. Only difference is that it uses row versioning rather than locking with the aim of providing read consistency.

When a statement runs in the READ_COMMITTED isolation level with READ_COMMITTED_SNAPSHOT option turned on, it sees snapshot of stable data as on start of current transaction. This is done by creating a row-versioned snapshot which will be used for any subsequent retrieval of data (reads). Since it uses the row-versioned snapshot any subsequent reads will not block writes and vice versa. In comparison, earlier isolation levels make use of locking which is the cause for concurrency issues. Please keep in mind that a write operation will still block another write operation to prevent any possible data corruption. This will help us to avoid most of the deadlocking situations and also prevents any dirty data from being read by our transaction.

You can turn this feature on at database level using below statement

ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON

Apply Operator

One of major features that I've been fascinated by in SQL Server 2005 has been the APPLY operator. This article discusses on some of the real time scenarios where you can make use of this feature. 

Scenario 1 - Running Total of a field

Consider the case where you need a running total to be calculated for a particular field. Here you can use the apply operator to calculate running total and return with each row.See below for illustration


declare @tbl table(Product_ID int identity(1,1),
Product_Name varchar(100)
)insert @tbl (Product_Name)select 'Boots' union allselect 'Peter England' union allselect 'Carona' union allselect 'Marks & Spencers'declare @tbl_items table(Item_ID int identity(1,1),
Product_ID int,
Item_Date datetime,
Price numeric(10,2)
)insert into @tbl_items (Product_ID,Item_date,Price)select 1,'20 dec 2009',150 union allselect 2,'13 Apr 2009',908 union allselect 1,'5 Mar 2009',1974 union allselect 3,'05 jan 2010',647 union allselect 2,'13 Jun 2009',832 union allselect 3,'19 May 2008',413 union allselect 3,'13 May 2009',235 union allselect 1,'23 Oct 2009',630 union allselect 1,'30 Aug 2009',110
select Product_Name,Item_date,Price, Totalfrom @tbl tjoin @tbl_items tion ti.Product_ID=t.Product_IDcross apply (select sum(Price) as Total
from @tbl_items
where Product_ID=ti.Product_ID
and Item_date<=ti.Item_date) t1Order by t.Product_ID,Item_Date

Scenario 2 - Get most recent or most ancient related data in case of 1-to -n relationship

Consider the case where you have one to many relationship existing between two tables and you want latest/earliest related detail from second table for every row of first table. In this case of you can use apply operator to achieve the result.

As an example using sample data from top, you can use below query to get latest sale detail of each product

select Product_Name,Item_date,Pricefrom @tbl tcross apply (select top 1 Item_date,Price
from @tbl_items
where Product_ID=t.Product_ID
Order by Item_date desc) t1Order by t.Product_ID

Result

Scenario 3 - Get concatenated string list of related field

Consider the case where you need to return a comma separated value of field which is related to master table field. Here also you can use apply operator coupled with for xml path to get the related values as csv list.

DECLARE @Student table(Student_ID int identity(1,1),Student_name varchar(100)
)INSERT @Student (Student_Name)SELECT 'Ram' union allSELECT 'Sita' union allSELECT 'Murali' union allSELECT 'Siddharth' union allSELECT 'Jacob'declare @Subjects table(Subject_ID int identity(1,1),
Subject_Name varchar(100)
)INSERT @Subjects (Subject_Name)SELECT 'Maths' union allSELECT 'English' union allSELECT 'Biology' union allSELECT 'Physics' union allSELECT 'Chemistry' union allSELECT 'Hindi'DECLARE @Student_subjects table(ID int identity(1,1),Student_ID int,
Subject_ID int
)INSERT INTO @Student_Subjects (Student_ID,Subject_ID)SELECT 1,1 union allSELECT 3,2 union allSELECT 4,1 union allSELECT 4,3 union allSELECT 4,5 union allSELECT 2,1 union allSELECT 3,1 union allSELECT 2,3 union allSELECT 1,3 union allSELECT 2,4 union allSELECT 5,3
SELECT s.Student_Name,left(t.u,len(t.u)-1)FROM @Student scross apply (select sub.Subject_name + ','
from @Subjects sub
inner join @Student_subjects ss
on ss.Subject_ID=sub.Subject_ID
where Student_ID=s.Student_ID
for xml path(''))t(u)

Scenario 4 - Call a table valued UDF passing each row value to do some processing

Consider the case where you need to parse a comma separated value in a field stored in a table field. Here also you can use apply operator to pass values to udf and return the individual values delimited by comma for each row of table.

Week Number in a Month

Here are 2 different ways, both are assuming the week starts on monday

If you want weeks to be whole, so they belong to the month in which they start: So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:

declare @date datetime = '2012-09-01'
select datepart(day, datediff(day, 0, @date)/7 * 7)/7 + 1

If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:

declare @date datetime = '2012-09-01'
select datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, @date), 0)), 0), @date - 1) + 1




ALLOW_SNAPSHOT_ISOLATION & SET READ_COMMITTED_SNAPSHOT ON

Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation. Fast fwd to 41:45 in the video to get to the part where she answers your question.

If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits.

If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code. MS SQL Server automatically applies snapshot isolation for that table.

I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.

A quick look at performance overhead using Snapshot Isolation.

Good article on how snapshot isolation can change the expected behavior of your app. It shows examples of how a update statement and a select statement might return totally different and unexpected results.

Database Configuration Option

AUTO CLOSE OPTION SHOULD BE OFF

Auto Close deallocates all objects from memory for a specified database.

Query:
SELECT name, is_auto_close_on FROM Sys.Databases

Alter database DB set AUTO_CLOSE OFF;

AUTO SHRINK OPTION SHOULD BE OFF

Auto Shrink automatically shrinks DB every 30 minutes , but can cause severe fragmentation.

Query:
SELECT name, is_auto_shrink_on FROM Sys.Databases

Alter database DB set AUTO_SHRINK OFF;

RECOVERY MODELS

Query:
SELECT name, recovery_model_desc FROM Sys.Databases

Alter database DB set recovery SIMPLE;

There are 3 types of Recovery Model:
1. SIMPLE - No Log Backups, Full Only.
2. BULK_LOGGED - Logs and Differentials, But Not PiT*.
3. FULL - Adds PiT*.
* PiT - Point in Time.


STATISTICS

Statistics used by SQL Server to decide which index to use and needs to be maintained to be effective. (Statistics rebuild after 500 records and then table growth by 20%).

There are 2 types of Statistics:

1. Synchronous - forces query to wait when stats are out of date.
2. Asynchronous - allow query to continue with old stats and build new ones in the background.

So, Asynchronous  Statistics should be ON.

Query:
SELECT name, is_auto_update_stats_on,is_auto_update_stats_async_on FROM Sys.Databases

Alter database DB set AUTO_UPDATE_STATISTICS_ASYNC ON;

Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL

SQL Server Primary Key and Unique Constraint Creation Script

The following script is for the creation of all Primary Keys and Unique Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS.
declare @SchemaName varchar(100)
declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique_constraint varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int 
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
declare @is_primary_key varchar(100)

declare CursorIndex cursor for
 select schema_name(t.schema_id) [schema_name], t.name, ix.name,
 case when ix.is_unique_constraint = 1 then ' UNIQUE ' else '' END 
    ,case when ix.is_primary_key = 1 then ' PRIMARY KEY ' else '' END 
 , ix.type_desc,
  case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
 + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
 + case when ix.allow_row_locks=1 then  'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
 + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
 + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
 + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
 , FILEGROUP_NAME(ix.data_space_id) FileGroupName
 from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 where ix.type>0 and  (ix.is_primary_key=1 or ix.is_unique_constraint=1) --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
 order by schema_name(t.schema_id), t.name, ix.name
open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName
while (@@fetch_status=0)
begin
 declare @IndexColumns varchar(max)
 declare @IncludedColumns varchar(max)
 set @IndexColumns=''
 set @IncludedColumns=''
 declare CursorIndexColumn cursor for 
 select col.name, ixc.is_descending_key, ixc.is_included_column
 from sys.tables tb 
 inner join sys.indexes ix on tb.object_id=ix.object_id
 inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
 inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
 where ix.type>0 and (ix.is_primary_key=1 or ix.is_unique_constraint=1)
 and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
 order by ixc.index_column_id
 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn
 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
    set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 
     
  fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
 end
 close CursorIndexColumn
 deallocate CursorIndexColumn
 set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
--  print @IndexColumns
--  print @IncludedColumns

set @TSQLScripCreationIndex =''
set @TSQLScripDisableIndex =''
set  @TSQLScripCreationIndex='ALTER TABLE '+  QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ADD CONSTRAINT ' +  QUOTENAME(@IndexName) + @is_unique_constraint + @is_primary_key + +@IndexTypeDesc +  '('+@IndexColumns+') '+ 
 case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  

print @TSQLScripCreationIndex
print @TSQLScripDisableIndex

fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName

end
close CursorIndex
deallocate CursorIndex

Script to Drop all SQL Server Primary Key and Unique Constraints

The following script is to drop all Primary Keys and Unique Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE DROP SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS.
DECLARE @SchemaName VARCHAR(256)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR FOR
SELECT  schema_name(t.schema_id), t.name,  i.name 
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
and (is_primary_key=1 or is_unique_constraint=1)

OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
WHILE @@fetch_status = 0
BEGIN
  SET @TSQLDropIndex = 'ALTER TABLE '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' +QUOTENAME(@IndexName)
  PRINT @TSQLDropIndex
  FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
END

CLOSE CursorIndexes
DEALLOCATE CursorIndexes

SQL Server Foreign Key Constraint Creation Script

The following script is for the creation of all Foreign Keys Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
declare @ForeignKeyID int
declare @ForeignKeyName varchar(4000)
declare @ParentTableName varchar(4000)
declare @ParentColumn varchar(4000)
declare @ReferencedTable varchar(4000)
declare @ReferencedColumn varchar(4000)
declare @StrParentColumn varchar(max)
declare @StrReferencedColumn varchar(max)
declare @ParentTableSchema varchar(4000)
declare @ReferencedTableSchema varchar(4000)
declare @TSQLCreationFK varchar(max)
--Written by Percy Reyes www.percyreyes.com
declare CursorFK cursor for select object_id--, name, object_name( parent_object_id) 
from sys.foreign_keys
open CursorFK
fetch next from CursorFK into @ForeignKeyID
while (@@FETCH_STATUS=0)
begin
 set @StrParentColumn=''
 set @StrReferencedColumn=''
 declare CursorFKDetails cursor for
  select  fk.name ForeignKeyName, schema_name(t1.schema_id) ParentTableSchema,
  object_name(fkc.parent_object_id) ParentTable, c1.name ParentColumn,schema_name(t2.schema_id) ReferencedTableSchema,
   object_name(fkc.referenced_object_id) ReferencedTable,c2.name ReferencedColumn
  from --sys.tables t inner join 
  sys.foreign_keys fk 
  inner join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id
  inner join sys.columns c1 on c1.object_id=fkc.parent_object_id and c1.column_id=fkc.parent_column_id 
  inner join sys.columns c2 on c2.object_id=fkc.referenced_object_id and c2.column_id=fkc.referenced_column_id 
  inner join sys.tables t1 on t1.object_id=fkc.parent_object_id 
  inner join sys.tables t2 on t2.object_id=fkc.referenced_object_id 
  where fk.object_id=@ForeignKeyID
 open CursorFKDetails
 fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
 while (@@FETCH_STATUS=0)
 begin    
  set @StrParentColumn=@StrParentColumn + ', ' + quotename(@ParentColumn)
  set @StrReferencedColumn=@StrReferencedColumn + ', ' + quotename(@ReferencedColumn)
  
     fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
 end
 close CursorFKDetails
 deallocate CursorFKDetails

 set @StrParentColumn=substring(@StrParentColumn,2,len(@StrParentColumn)-1)
 set @StrReferencedColumn=substring(@StrReferencedColumn,2,len(@StrReferencedColumn)-1)
 set @TSQLCreationFK='ALTER TABLE '+quotename(@ParentTableSchema)+'.'+quotename(@ParentTableName)+' WITH CHECK ADD CONSTRAINT '+quotename(@ForeignKeyName)
 + ' FOREIGN KEY('+ltrim(@StrParentColumn)+') '+ char(13) +'REFERENCES '+quotename(@ReferencedTableSchema)+'.'+quotename(@ReferencedTable)+' ('+ltrim(@StrReferencedColumn)+') ' + char(13)+'GO'
 
 print @TSQLCreationFK

fetch next from CursorFK into @ForeignKeyID 
end
close CursorFK
deallocate CursorFK

Script to Drop all SQL Server Foreign Key Constraints

The following script is to drop all Foreign Key Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE DROP SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
declare @ForeignKeyName varchar(4000)
declare @ParentTableName varchar(4000)
declare @ParentTableSchema varchar(4000)

declare @TSQLDropFK varchar(max)

declare CursorFK cursor for select fk.name ForeignKeyName, schema_name(t.schema_id) ParentTableSchema, t.name ParentTableName
from sys.foreign_keys fk  inner join sys.tables t on fk.parent_object_id=t.object_id
open CursorFK
fetch next from CursorFK into  @ForeignKeyName, @ParentTableSchema, @ParentTableName
while (@@FETCH_STATUS=0)
begin
 set @TSQLDropFK ='ALTER TABLE '+quotename(@ParentTableSchema)+'.'+quotename(@ParentTableName)+' DROP CONSTRAINT '+quotename(@ForeignKeyName)+ char(13) + 'GO'
 
 print @TSQLDropFK

fetch next from CursorFK into  @ForeignKeyName, @ParentTableSchema, @ParentTableName
end
close CursorFK
deallocate CursorFK