Concatenate SQL Server Columns into a String with CONCAT()

 

Problem

I need to produce mailing labels from my Microsoft SQL Server database so I am using the + sign to concatenate the first, middle, and last names together. The issue I see is I get NULL for a lot of rows. This makes me unable to produce the full names. What are some options to address this problem? Check out this tutorial to learn more about concatenating data in SQL Server with T-SQL string concatenation.

Solution

Prior to SQL Server 2012 concatenation was accomplished by using the plus (+) sign to concatenate fields together of various data types (varchar, char, int, numeric, etc.). The limitation of this method is if any of the fields you are concatenating are NULL, the final string value is NULL. In SQL Server 2012 and later there is the CONCAT() function that replaces NULL with an empty string. Take a look at this tip to see how this new function works and how it can be beneficial in your code.

For this demo I am going to use the Person.Person table from the AdventureWorks2012 database to demo the SQL functions to generate a full name for creating mailing labels. First, the following example is the old technique to concatenate strings using the + sign (concatenation operator):

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName as MailingName
FROM Person.Person

As you can see in the screen shot below the MailingName is NULL for any row that has NULL for any one of the name columns. The only rows that have MailingName filled in have a value for all the title, firstname, middlename, and lastname columns. This could be corrected by wrapping ISNULL(column,'') around all the columns in the concatenated field to account for any values having nulls, but that code gets long, messy, and hard to read.

Below is example syntax is using ISNULL along with the plus sign to concatenate values. The ISNULL function will replace NULL values with the value noted in the second parameter, which in this example is an empty string.

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    ISNULL(Title,'') + ' ' + ISNULL(FirstName,'') + ' ' + ISNULL(MiddleName,'') + ' ' + ISNULL(LastName,'') as MailingName
FROM Person.Person

As you can see in the example below, the MailingName is no longer NULL as it replaced the NULL values with an empty string. This achieves the same as using the CONCAT() function, but requires a lot more code and readability.

The next set of code is using the new CONCAT() function that is in SQL Server 2012 and later versions with a SELECT statement. It replaces NULL values with an empty string of type VARCHAR(1). This SQL statement is much easier to read and write when you need to have NULL code handling in place and generate a single string in a single column with spaces as a separator.

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    CONCAT(Title,' ',FirstName,' ',MiddleName,' ',LastName) as MailingName
FROM Person.Person

If you see the results of this, all MailingName values are present, even if they have some of the columns set to NULL.

As you can see this new function is very handy and behaves much different that the old form of concatenation. Instead of evaluating to NULL if any if the columns contain NULL values, the CONCAT() function replaces it with an empty string. This is very useful for coding around NULL values.

Managing Maximum Number of Concurrent Connections in SQL Server

 

Problem

By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance. However, in some cases, database performance can be affected by such a huge number of users simultaneously querying the instance, which is not desirable.

Solution

To set a limit on the number of users simultaneously accessing the instance, we can use "Maximum number of concurrent connections" property in SQL Server. The value for this property can be set via SQL Server Management Studio as well as via a T-SQL statement.

In this article, we will see both the ways.

Configure Max Number of Concurrent Connections in SQL Server Management Studio

To limit the maximum number of concurrent connections via SQL Server Management Studio, follow these steps.

1. From SQL Server Management Studio, right click on the SQL Server instance that you want to set a maximum number of connections for and click the "Properties" option at the bottom. This is shown in the following screenshot:

2. A new window will appear. Select, the "Connections" option from the set of options on the left as shown in the following figure:

By default, the value for "Maximum number of concurrent connections" is set to 0 which means that an unlimited number of users can connect. The actual number depends upon the type of hardware and the version of SQL Server.

1. In the "Maximum number of concurrent connections" scroll box, enter the number of maximum concurrent connections that you want for your database server. For the sake of this article, set the number of maximum number of concurrent connections to 300 and click OK.
2. Restart the SQL Server Instance, refresh it and again go to Properties > Connections and you will see 300 in "Maximum number of concurrent connections" scroll box.

Dedicated Administrator Connection

If the maximum number of concurrent connections that SQL allows has already been reached, you will not be able to login to the SQL Server and change the configuration settings. In such cases you can try to connect to SQL Server via Dedicated Administrator Connection (DAC). Here is an article explaining how to connect to SQL Server using DAC. The DAC allows you to connect to SQL Server even when it cannot accept any more standard connections. Once you are connected to SQL Server using DAC, open SQL Server Management Studio execute the above steps and see if you can modify the maximum number of concurrent connections.

SQL Server T-SQL Code to Manage Maximum Number of Concurrent Connections

Apart from using SQL Server Management Studio, the maximum number of concurrent connections can also be controlled via a Transact-SQL script. To do so, you need to execute the following steps.

1 - Enable Advanced Configuration Options

Changing the maximum number of concurrent connections is a very advanced configuration task and is normally performed by Database Administrators. Therefore, before we can modify the maximum number of concurrent connections, we need to enable the "show advanced options" configuration. The following script enables this configuration:

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

In the script above, we execute the system stored procedure "sp_configure" and pass it two parameters: "show advanced option" and 1. The first parameter tells SQL Server that we want to change settings for "show advanced option" configuration. The second parameter enables the "show advanced options".

The flag "RECONFIGURE WITH OVERRIDE" sets the permissions for the configuration change. Here it means that the configuration changes executed by the user will override any existing configuration settings.

When the above statement executes, you will see the following output:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

The output shows that the configurations have successfully been updated.

2 - Change Concurrent Number of Connections

Now we have access to the configuration options that can change the number of concurrent connections. The next step is to write the script that updates the number of connections. The following script does that:

EXEC sys.sp_configure N'user connections', N'500'

In the above script, we again use the system stored procedure "sp_configure", but this time we pass it "user connections" as a parameter, which tells the parameter the stored procedure that this time we want to configure the "user_connections" settings. The second parameter is the number of concurrent user connections, which is 500 in the above script. Once you execute the above script, you will see the following output:

Configuration option 'user connections' changed from 300 to 500. Run the RECONFIGURE statement to install.

From the output, you can see that the maximum number of concurrent connections has been updated from 300 to 500. Remember, in the last section we set the total number of user connections to 300 using SQL Server Management Studio.

To make sure that your changes have actually taken effect, restart your database server and again go to Properties > Connections. You should see that the value in the "Maximum number of concurrent connections" scroll box has been updated to 500 as shown below:

3 - Disable Advanced Configuration Options

Once the advanced value for maximum concurrent connections has been set, as the last step you should always disable the advanced configuration options since they can greatly affect the behavior SQL Server. To disable advanced configuration options, execute the following script:

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

To disable advanced configuration options, we simply need to pass ‘0’ as the second parameter to the "sp_configure" stored procedure. The first parameter remains the same i.e. "show_advanced" options.

Once you execute the script above, you will see the following message on the console:

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

The message shows that the "show advanced options" configuration has been disabled.

Find Last Restart of SQL Server

 

Problem

How can I tell when my SQL Server instance was last started?  In this tip we look at various methods of how to get the start time for a SQL Server instance.

Solution

Here are a few different ways that you can tell when SQL Server was last started.

Method 1: Find Last Start Time for SQL Server using sys.dm_os_sys_info

This DMV, sys.dm_os_sys_info, contains a column that holds the startup time for SQL Server. You can run the following command to find the start time.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Method 2: Find Last Start Time for SQL Server using SQL Server Error Log

If you open the current SQL Server error log in SSMS under Management > SQL Server Logs and scroll to the bottom of the error log you can see when the server was started. Note the highlighted text below "SQL Server is starting".  You will want to look for this to make sure the error log was not cycled and give you a false impression of the start time.

Here is an example of a cycled error log and note the highlighted text.   If you see this you will need to look at a previous error log until you find the log that contains the information in the image above.

Note: if you are running the Express edition of SQL Server you cannot read the error logs directly from SSMS, but you can use xp_readerrorlog or navigate to the folder where the log files are stored and use a text editor to read the SQL Server error log files.

Method 3: Find Last Start Time for SQL Server using Dashboard Reports

Another option is to use the SQL Server Dashboard Report.  Right click on the server name in SSMS and select Reports > Server Reports > Server Dashboard and you will get a report similar to the following.

Method 4: Find Last Start Time for SQL Server using Windows Event Viewer

Another option is to use Windows Event Viewer to look for the startup time.  If you open Windows Event Viewer and filter your Event Sources (in our case MSSQL$SQL2008) for the SQL Server instance and Event ID 17162 you can find all of the startup times that are still in the Event Viewer log.

Here is a partial listing of the startup times for this instance.

Resolving could not open a connection to SQL Server errors

 

Problem

Sometimes you may have issues connecting to SQL Server and you may get messages such as the following:

ERROR: (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

Or

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

Or

A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)

These errors could be for either Named Pipes connections or TCP/IP connections. In this tip, we look at what may be causes to these errors and how to resolve.

Solution

There could be several reasons you get these error messages. Follow the below steps to see if you can resolve the issue.

Step 1 - Check that you can ping the SQL Server box

Make sure you are able to ping the physical server where SQL Server is installed from the client machine. If not, you can try to connect to the SQL Server using an IP Address (for default instance) or IP Address\Instance Name for a named instance.

If it resolves using an IP address, you can add the SQL Server machine into /etc/host file. To add the entry in the /host file type %SystemRoot%\system32\drivers\etc\ in the run window and open the host file using Notepad. In the below image I added IP address 74.200.243.253 with a machine name of SQLDBPool. Now I should be able to use the machine name instead of the IP address to connect to the SQL Server.

Step 2 - Check that the SQL Services are running

Make sure the SQL services are running You can check the SQL Server services by using the SC command or SQL Server Configuration Manager. Many times you may find that the SQL Server instance is not running.

Using SQL Server Configuration Manager

You can use SQL Server Configuration Manager to make sure the services are running.  If for some reason you cannot find SQL Server Configuration Manager check out this article.

Using SC command

From a Windows command line you can issue the following command to see the status of the services.

Please note for a named instance you have to write the command as follows using the correct instance name, by replacing instancename with the actual SQL Server instance name.

sc query mssql$instancename

Step 3 - Check that the SQL Server Browser service is running

Check that the SQL Server Browser service is running. If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port. To resolve this you will need to have the SQL Server Browser service enabled and running. You can check the browser service status using either SQL Server Configuration Manager or the SC command as follows.

Step 4 - Check that you are using the correct SQL Server instance name

Make sure you are using the correct instance name. When you connect to a default instance, machinename is the best representative for the instance name and when you connect to a named instance such as sqlexpress, you need to specify the instancename as follows: machinename\instancename where you enter the SQL Server instance name for instancename.

Step 5 - Check that you can find the SQL Server

Check that SQL Server is in the network. You can use the SQLCMD -L command to retrieve the list of SQL Servers installed in the network. Note that this will only return SQL Servers if the SQL Server Browser service is running.

Step 6 - Check that TCP/IP and Named Pipes are enabled

Check the TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes and TCP/IP protocol.

For the TCP/IP protocol, right click and select properties to check the TCP/IP communication port as well. The default port is 1433, which can be changed for security purposes if needed.

Step 7 - Check that allow remote connections for this server is enabled

Check to see if allow remote connections for this server is enabled. In SSMS, right click on the instance name and select Properties. Go to the Connections tab and make sure Allow remote connections to this server is checked. If you need to make a change, you must restart the SQL Server instance to apply the change.

You can also configure the remote server connections using the below commands. If you make changes you will need to restart SQL Server for these to take affect.

The settings below are equivalent to the settings in the image above.

exec sp_configure "remote access", 1          -- 0 on, 1 off
exec sp_configure "remote query timeout", 600 -- seconds
exec sp_configure "remote proc trans", 0      -- 0 on, 1 off

Step 8 - Check the port number that SQL Server is using

 Locally connect to SQL Server and check the error log for the port entry. You can execute XP_READERRORLOG procedure to read the errors or use SSMS by going to Management > SQL Server Logs and select the Current log. Scroll to the bottom which will be the first entries in the error log and look for entries similar to below that shows Named Pipes and TCP/IP are enabled and the port used for TCP/IP which is 1433 in this case.

Step 9 - Check that the firewall is not blocking access to SQL Server

Configure the Windows Firewall for the SQL Server port and SQL Server Browser service. Go to Control Panel and click on  Windows Firewall.  Go to exceptions tab as shown below. You can read this tip Configure Windows Firewall to Work with SQL Server for more information.

Click on Add Port... and enter the port number and name.

Click on Add Program... to add the SQL Server Browser service. Here you need to get the browser service executable path, normally it is located at C:\Program Files\Microsoft SQL Server\90\Shared location for SQL 2005 or similar for other versions of SQL Server. Browse the location and add the SQLBrowser.exe in the exception list.

Step 10 - Check that the Service Principal Name has been registered

If you are able to connect to SQL Server by physically logging on to the server, but unable to connect from a client computer then execute the below query in a query window to check the SPN.

-- run this command to see if SPN is not found
EXEC xp_readerrorlog 0,1,"could not register the Service Principal Name",Null

If the SPN is not found, read this Register a Service Principal Name for Kerberos Connections and this How to Configure an SPN for SQL Server Site Database Servers for more details about how to setup and register an SPN.

Enforcing business rules using SQL Server CHECK constraints

 

Problem

I am designing a table and I am trying to enforce domain integrity on some of my columns using CHECK constraints. Can you give me some examples of using CHECK constraints to constrain a single column and to constrain a column based on the value in another column?

Solution

If you're not familiar with CHECK constraints, they're used to enforce column domain integrity by limiting the values that may be inserted into a column. I find that CHECK constraints are woefully under used in most data models. In a lot of cases, I've seen a trigger used to enforce a business rule that could have been enforced using a CHECK. I personally use CHECKs over triggers for a variety of reasons. For one reason, I don't need to waste time to write specialized code. Another reason is that CHECK constraints are enforced before a database modification is made by the database engine whereas a trigger is checked after the fact. Using triggers extends the life of a transaction and can potentially be expensive if a ROLLBACK condition is detected.

SQL Server allows you define column level CHECK constraints which check the integrity of a single column. Furthermore, SQL Server allows you to check the values of multiple columns together using a "table level" CHECK constraint. Though they're labeled as "table level" constraints, these type of CHECK constraints are actually checked at the row level. Lastly, CHECK constraints work by examining a defined condition to see if it evaluates to either TRUE or FALSE.

Let's move on to some examples. I'll set up a sample table called EMPLOYEE. As part of our table definition, we'll enforce a company rule that no employee in our system is allowed to earn more than $100,000 or less than $30,000.

CREATE TABLE DBO.EMPLOYEE
(
EMPLOYEEID INT IDENTITY(1,1) NOT NULL,
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50) NOT NULL,
IS_MANAGER BIT NULL,
SALARY MONEY NOT NULL,
BONUSPCT FLOAT NOT NULL
)
GO
ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEEID)
GO
ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT DF_EMPLOYEE_BONUSPCT DEFAULT 0.00 FOR BONUSPCT
GO 
ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT CK_EMPLOYEE_SALARY CHECK (SALARY BETWEEN 30000 AND 100000)
GO 
EXEC SP_HELPCONSTRAINT EMPLOYEE
GO 

Examining the constraints in our table, we see our simple, column level constraint defined:

Trying to insert values into the SALARY column that fall outside our defined range are successfully caught by the database engine.

INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, SALARY)
SELECT 'GEORGE', 'WASHINGTON', 110000
GO 

So far, so good. However, there's another company rule that we need to enforce. Managers in our company can earn any bonus percentage but they are the only employee type that is allowed to earn a bonus of 5 or more percent of his/her salary. Non-managers can earn any amount provided it's less than 5%. Let's try to enforce this company rule via a table CHECK constraint:

ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT 
CHECK (IS_MANAGER = 1 AND BONUSPCT >= 5.00)
GO 
INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'GEORGE', 'WASHINGTON', 1, 100000, 5.00
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'BEN', 'FRANKLIN', 0, 75000, 2.50
GO

What happened? Employee Washington was inserted ok but Franklin failed. Well, the CHECK constraint as it's defined forces all employees to be both a manager and to have a bonus percentage greater than or equal to 5. That's great if all employees must be managers with a bonus of 5% or greater. However, that's not the business rule we're trying to implement. We're trying to make sure that if the employee is not a manager, he/she cannot exceed the 5% bonus threshold. So how can we have the CHECK constraint enforce integrity conditionally? You might get tempted to use a trigger. However, the CHECK constraint supports CASE expressions. Furthermore, as I mentioned at the beginning of this tip, CHECKs are examined for TRUE/FALSE conditions. As a result, you can leverage these two together to produce a CHECK constraint that can perform conditional checking. Let's re-create our CHECK using these principles and try to re-insert employee Franklin.

ALTER TABLE DBO.EMPLOYEE
DROP CONSTRAINT CK_EMPLOYEE_BONUSPCT
GO 
ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT 
CHECK (CASE WHEN IS_MANAGER <> 1 AND BONUSPCT >= 5.00 THEN 1 ELSE 0 END = 0)
GO 
INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'BEN', 'FRANKLIN', 0, 75000, 2.50
GO

Now trying to INSERT Franklin with the corrected CHECK constraint definition results in success.

Now let's try to INSERT a new employee, Jefferson.

INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'THOMAS', 'JEFFERSON', NULL, 80000, 7.50
GO

Wait a second, Jefferson was not flagged as a manager but the engine allowed his bonus of 7.5%! Why did this happen? Again, recall that CHECK constraints are examined for TRUE/FALSE conditions. Looking at our schema, the IS_MANAGER column is declared as NULL. Any NULL values in this column will cause the IS_MANAGER condition in the CHECK to equate to unknown and cause the CASE expression to evaluate to our default boolean value for success (zero). When working with nullable columns, this is a gotcha to be aware about. There are a couple of ways to correct this. One is to define the IS_MANAGER flag as NOT NULL, converting the NULL values to zero. If you cannot change the model, the other is to re-write the CASE to account for NULL IS_MANAGER flags. Below is one way to re-write the CASE (you'll probably have your own variation; my version is for illustrative purposes).

TRUNCATE TABLE DBO.EMPLOYEE
GO
ALTER TABLE DBO.EMPLOYEE
DROP CONSTRAINT CK_EMPLOYEE_BONUSPCT
GO 
ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT 
CHECK (CASE WHEN IS_MANAGER IS NULL AND BONUSPCT >= 5.00 THEN 1 
            WHEN IS_MANAGER <> 1 AND BONUSPCT >= 5.00 THEN 1
            ELSE 0 END = 0)
GO 
INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'JAMES', 'MADISON', NULL, 60000, 5.50
GO

Executing this script for a new employee, Madison, we see that the engine now successfully captures this condition

Now you're probably thinking "I bet I could write a scalar function to read my entire table and perform a true table level CHECK to check for sums and aggregates being exceeded". You're right, you could, but there are risks involved.

As you can see, CHECK constraints are a powerful way to enforce single and multi-column domain integrity without needing to write specialized triggers or stored procedure code.

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

 

Problem

As SQL Server DBAs we may need to generate a script for the creation of all Primary Keys, Unique and Foreign Key constraints. We know with the SQL Server native tools that there is not a way to meet this need all at the same time. In this tip we look at two simple scripts to generate the SQL Server Primary Keys, Unique and Foreign Key constraints and also provide two scripts for dropping these constraints.

Solution

Common SQL Server constraints are for Primary and Foreign Keys as well as Unique constraints.  They are very important for data integrity in SQL Server databases. This is why we need to be able to take a backup of each type of constraint in an efficient manner so that we can recreate them in case they are dropped by accident or if you need to recreate the same constraints in another copy of the same database for testing, development or training purposes. The scripts below have been written in SQL Server 2014 but they should also work on SQL Server 2005/2008/2008R/2012.

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