Space occupied by each Schema in SQL Server database

SELECT  SCHEMA_NAME(so.schema_id) AS SchemaName
               ,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMB
        FROM    sys.dm_db_partition_stats ps
        JOIN    sys.indexes i
          ON    i.object_id                                     =           ps.object_id
         AND    i.index_id                                      =           ps.index_id
JOIN sys.objects so
  ON i.object_id = so.object_id
       WHERE    so.type = 'U'
    GROUP BY so.schema_id
    ORDER BY OBJECT_SCHEMA_NAME(so.schema_id), SizeInMB DESC

T SQL query to find when was the database backed up lastly on SQL Server

Select database_name As [Database Name]
, [name] As [Backup Name]
, Case When Type = 'D' Then 'Full Backup'
When Type = 'I' Then 'Differential Backup'
When Type = 'L' Then 'Log Backup'
Else 'File or filegroup or partial'
End As [Backup Type]
, Recovery_Model
, [backup_start_date] As [Time of the SQL Backup Job]
FROM [msdb].[dbo].[backupset]

SQL Script to find all Date/time columns in database

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')

Difference between Detaching database and bringing database Offline

SQL Server Offline and Detach Database




Detach Database/Attach Database: The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.

you can attach a copied or detached SQL Server database. When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2016 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.

The requirement for attaching log files depends partly on whether the database is read-write or read-only, as follows:

• For a read-write database, you can usually attach a log file in a new location. However, in some cases, reattaching a database requires its existing log files. Therefore, it is important to always keep all the detached log files until the database has been successfully attached without them.
  If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. If it is found, the old log file is used, regardless of whether the database was shut down cleanly. However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.
• If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only. For a read-only database, the log file or files must be available at the location specified in the primary file of the database. A new log file cannot be built because SQL Server cannot update the log location stored in the primary file.

Database Offline: Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.


Use OFFLINE and ONLINE
1) If you are trying to make the database temporary unavailable for a period of time, you could take the database OFFLINE, and make it available by bringing it ONLINE whenever it is or you are ready.

2a) If you want to move the database or log files to different physical location or changing the database and/or log physical file name, AND keep them within the same SQL Server instance.
- Before you take the database offline, you need to know the logical name of the file. The name field is the logical name for the physical file name that you are about to change the path. Record the logical name for later use.

SELECT name, type, type_desc, physical_name, state, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabase')

- Take the database OFFLINE.
ALTER DATABASE YourDatabase SET OFFLINE
WITH ROLLBACK IMMEDIATE;

-Move the database and/or log files to different location.
- Update the database and/or log files path registered on its SQL Server instance. Execute the T-SQL command for each database and log files,

ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourFileLogicalName,
FILENAME = 'Your Database or Log new physical path')

Bring the database ONLINE.
ALTER DATABASE YourDatabase SET ONLINE;

If you just want to change the logical name, you dont have to take the database OFFLINE. You could change it on SSMS or T-SQL.

Right click on the database > Properties > Select Page > Change it under Logical Name column > OK.

ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourFileLogicalName,
NEWNAME = YourFileNewLogicalName)

2b) If you are relocating a file during part of the scheduled disk maintenance process, you may need to change the file path registered on its SQL Server instance before taking the database OFFLINE. This assure the server has the right path for the database and log files when the instance restart, after being shut down for maintenance.
- When the database and/or log file path is successfully updated on the instance, a message will show; 'The file "YourDatabaseFile" has been modified in the system catalog. The new path will be used the next time the database is started.'

Use DETACH and ATTACH,
1) If you want to move the database and log files to a different SQL Server instance or another server.
- Rollback active transaction and gain exclusive access. Detach the database. (optional: update statistics) 

ALTER DATABASE YourDatabase SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

EXEC sp_detach_db 'YourDatabase', 'true'

- Move database and log files to different location.
- Attach the database on different instance on same/another server. 

USE master;

CREATE DATABASE YourDatabase
ON (FILENAME = 'the physical path of your database file')
LOG ON (FILENAME = 'the physical path of your log file')
FOR ATTACH;

- If the database has previously set to single user, you may want to set it to multiple user access option.

ALTER DATABASE YourDatabase SET MULTI_USER

2) Even though you can choose to detach and attach the database back on the same SQL Server instance for scenario like changing physical path or file name, it is recommended to use the OFFLINE and ONLINE methods due to the restriction and limitation on DETACH. When you detach a database, you remove the database from the instance. It is required to remove the database from any participation of replication, mirroring and snapshot.

Reference: 

Move and Rename File with File System Task in SSIS

Move and Rename File with File System Task

All Credit goes to Katie & Emil Youtube Channel

Script to find SQL Server Engine Edition

Finding SQL Server EngineEdition:

SELECT SERVERPROPERTY('EngineEdition');

Database Engine edition of the instance of SQL Server installed on the server.
1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.)
2 = Standard (This is returned for Standard, Web, and Business Intelligence.)
3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.)
4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services)
5 = SQL Database

Description:

The EngineEdition property returns a value of 2 through 5

Value 1: 1 isn’t a valid value in versions after SQL Server 2000,

Value 2: If value is 2, edition is either Standard, Web, or Business Intelligence, and fewer features are available. The features in Enterprise edition (as well as in Developer and Enterprise Evaluation editions) that aren’t in Standard edition generally relate to scalability and high-availability features, but other Enterprise-only features are available

Value 3: A value of 3 indicates that SQL Server edition is either Enterprise, Enterprise Evaluation, or Developer. These three editions have exactly the same features and functionality.

Value 4: A value of 4 for EngineEdition indicates that your SQL Server edition is Express, which includes SQL Server Express, SQL Server Express with Advanced Services, and SQL Server Express with Tools.

Value 5: Value of 5 for EngineEdition indicates that SQL Azure, a version of SQL Server that runs as a cloud-based service. Although many SQL Server applications can access SQL Azure with only minimum modifications because the language features are very similar between SQL Azure and a locally installed SQL Server.

How to find percentage of a database backup job done in SQL Server?

Script to find percentage of a database backup job is done

SELECT percent_complete
 ,*
FROM sys.dm_exec_requests
WHERE command IN (
  'RESTORE DATABASE'
  ,'BACKUP DATABASE'
  )

Elapsed time in Hours to complete the Job

SELECT command
 ,percent_complete
 ,'elapsed' = total_elapsed_time / 3600000.0
 ,'remaining' = estimated_completion_time / 3600000.0
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%'

Is it is possible to Limit the number of ErrorLog Files in SQL Server

Identify SQL Server Error Log File used by SQL Server Database Engine by Reading SQL Server Error Logs

SELECT SERVERPROPERTY('ErrorLogFileName')

Is it possible to limit the number of errorlog files less then 6 ?

Nope. Minimum number to configure is 6. We can delete all the archived errorlog files from server if we want but  ensure they are not required for auditing purposes and such.

we can set it to 6 and then create a scheduled job that simply calls

EXEC sp_cycle_errorlog

The more often we run this, the smaller error log files will have and will keep it to 6 error log files.

 clean up message by reviewing what messages are appearing. For example if it is "successful backup" messages you can enable trace flag 3226 globally for that instance and this will suppress those messages from being written all the time. Outside of that it is up to us to determine what is writing the message and figure out how to clean that up. An example of this that is most common is "failed login" messages for rogue service or application on a remote server.pplication on a remote server.

GRANT ALL in SQL Server

A GRANT ALL syntax also exists, granting supposedly all the permissions
on a securable. But it is better not to use it, because it does not in fact
grant all permissions, only the ones defined in the SQL-92 ANSI standard.
More permissions are available for SQL Server objects than the permissions
defined in the ANSI standard. The GRANT ALL syntax is now deprecated.

• If the securable is a database, "ALL" means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.
• If the securable is a scalar function, "ALL" means EXECUTE and REFERENCES.
• If the securable is a table-valued function, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
• If the securable is a stored procedure, "ALL" means EXECUTE.
• If the securable is a table, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
• If the securable is a view, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

List of Permissions and their Description in SQL Server

Permission name	Description
ALTER	Permission to modify the object's definition
CONNECT	Permission to access the database or connect to the endpoint
DELETE	Permission to delete the object
EXECUTE	Permission to execute the stored procedure or the function
IMPERSONATE	Permission to take the identity of a principal, by the means of an EXECUTE AS command
INSERT	Permission to insert data into the table or view
REFERENCES	Permission to reference the object in a foreign key definition, or to declare a view or function WITH SCHEMABINDING referencing the object
SELECT	Permission to issue a SELECT command against the object or column
TAKE OWNERSHIP	Permission to become the owner of the object
UPDATE	Permission to update the data
VIEW DEFINITION	Permission to view the definition (structure) of the object

Script to find what Permissions apply to what class of securables in SQL Server

SELECT *
FROM sys.fn_builtin_permissions(DEFAULT)
ORDER BY class_desc

Script to convert a database to contained database in SQL Server

What is Contained Database in SQL Server 2012?

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.
Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
All metadata are defined using the same collation.
User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.


To Set-up  contained databases  is a simple process which involves the following being carried out within SSMS:-

sp_configure 'contained database authentication'
 ,1
GO

RECONFIGURE

We can convert a non contained database to a contained database simply by setting its CONTAINMENT property,

USE [master]
GO

ALTER DATABASE [marketing]

SET CONTAINMENT = PARTIAL;

The users mapped to SQL logins can be converted to contained database users, using the sp_migrate_user_to_contained system
procedure 

SELECT 'EXEC sp_migrate_user_to_contained @username = N''' + dp.NAME + ''',
@rename = N''keep_name'',
@disablelogin = N''do_not_disable_login'' ;'
FROM sys.database_principals AS dp
INNER JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
 AND sp.is_disabled = 0;

This code returns execute statements copy it and execute

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.


If you move a non-contained database from one server to another, by means of backup/
restore or detach/attach, then there is a chance that your SQL users will become
orphaned, meaning that they will have no corresponding login. As the mapping between logins
and users is done by the SID, if a login is present on the destination instance with the same
name but another SID, then the user will not recognize it and will be orphaned.
If you are moving the database to another server in the same domain, the
user to login mapping problem occurs only with SQL logins, because the SID
used for Windows logins is the same as the domain SID set in Active Directory.
Thus it is the same on every instance where this login is created.

CREATE FILE encountered operating system error 5 (Access is denied.)

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)


Try to open SSMS (Sql Server Management Studio) with Run as ADMINISTRATOR, and then try again to then try again to attach

For More info:  http://www.mssqltips.com/sqlservertip/2528/database-attach-failure-in-sql-server-2008-r2/

SQl Server "How is My Memory Being used" Performance Counters"

Reference: http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

Restoring Orphaned MDF File in SQL Server

You can attach the database using CREATE DATABASE FOR ATTACH_REBUILD_LOG.

http://msdn.microsoft.com/en-us/library/ms176061.aspx

If that doesn't work, you can try the undocumented FOR ATTACH_FORCE_REBUILD_LOG.

CREATE DATABASE < YOURDBNAME > ON (FILENAME = 'D:\<YOURMDFNAME>.mdf')
FOR ATTACH_FORCE_REBUILD_LOG

From Management Studio

Attaching the .mdf file through the management console, without a .ldf file

1. right click "Databases" and select attach.
2. Click on "add" and select the .mdf file, click OK.
3. In the details panel select the .dlf file, click remove button.
4. Click the main OK button.

Script to delete millions of records without increasing your log size in SQL Server

DECLARE @continue INT
DECLARE @rowcount INT

SET @continue = 1

WHILE @continue = 1
BEGIN
 PRINT GETDATE()

 SET ROWCOUNT 10000     --Replace 10000 as required


 BEGIN TRANSACTION

 DELETE
 FROM dbo.Transactions
 WHERE TranDate IS NULL --Replace your delete script here

 SET @rowcount = @@rowcount

 COMMIT

 PRINT GETDATE()

 IF @rowcount = 0
 BEGIN
  SET @continue = 0
 END
END

Script to find number of rows in each partition in a partitioned table in SQL Server

SELECT t.NAME [table]
 ,p.rows
 ,p.partition_number
 ,v.boundary_id
 ,v.value
FROM sys.tables t
INNER JOIN sys.partitions p ON p.object_id = t.object_id
INNER JOIN sys.partition_range_values v ON v.boundary_id = p.partition_number
WHERE is_ms_shipped = 0
ORDER BY [table]

Script to list all SQL Server instance names in a server

SET NOCOUNT ON

DECLARE @CurrID INT
 ,@ExistValue INT
 ,@MaxID INT
 ,@SQL NVARCHAR(1000)
DECLARE @TCPPorts TABLE (
 PortType NVARCHAR(180)
 ,Port INT
 )
DECLARE @SQLInstances TABLE (
 InstanceID INT identity(1, 1) NOT NULL PRIMARY KEY
 ,InstName NVARCHAR(180)
 ,Folder NVARCHAR(50)
 ,StaticPort INT NULL
 ,DynamicPort INT NULL
 ,Platform INT NULL
 );
DECLARE @Plat TABLE (
 Id INT
 ,NAME VARCHAR(180)
 ,InternalValue VARCHAR(50)
 ,Charactervalue VARCHAR(50)
 )
DECLARE @Platform VARCHAR(100)

INSERT INTO @Plat
EXEC xp_msver platform

SELECT @Platform = (
  SELECT 1
  FROM @plat
  WHERE charactervalue LIKE '%86%'
  )

IF @Platform IS NULL
BEGIN
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

 UPDATE @SQLInstances
 SET Platform = 64
END
ELSE
BEGIN
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

 UPDATE @SQLInstances
 SET Platform = 32
END

DECLARE @Keyexist TABLE (Keyexist INT)

INSERT INTO @Keyexist
EXEC xp_regread 'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

SELECT @ExistValue = Keyexist
FROM @Keyexist

IF @ExistValue = 1
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

UPDATE @SQLInstances
SET Platform = 32
WHERE Platform IS NULL

SELECT @MaxID = MAX(InstanceID)
 ,@CurrID = 1
FROM @SQLInstances

WHILE @CurrID <= @MaxID
BEGIN
 DELETE
 FROM @TCPPorts

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPDynamicPorts'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPPort'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPDynamicPorts'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPPort'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 UPDATE SI
 SET StaticPort = P.Port
  ,DynamicPort = DP.Port
 FROM @SQLInstances SI
 INNER JOIN @TCPPorts DP ON DP.PortType = 'TCPDynamicPorts'
 INNER JOIN @TCPPorts P ON P.PortType = 'TCPPort'
 WHERE InstanceID = @CurrID;

 SET @CurrID = @CurrID + 1
END

SELECT serverproperty('ComputerNamePhysicalNetBIOS') AS ServerName
 ,InstName
 ,StaticPort
 ,DynamicPort
 ,Platform
FROM @SQLInstances

SET NOCOUNT OFF

Script to find number of users connected to SQL Server

SELECT COUNT(*) AS ConnectionCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1

Script to drop all Foreign keys and recreate them

--Create Table and Save Foreign Keys in a Table
--Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)

CREATE TABLE fklist (
 RowId INT PRIMARY KEY IDENTITY(1, 1)
 ,ForeignKeyConstraintName NVARCHAR(200)
 ,ForeignKeyConstraintTableSchema NVARCHAR(200)
 ,ForeignKeyConstraintTableName NVARCHAR(200)
 ,ForeignKeyConstraintColumnName NVARCHAR(200)
 ,PrimaryKeyConstraintName NVARCHAR(200)
 ,PrimaryKeyConstraintTableSchema NVARCHAR(200)
 ,PrimaryKeyConstraintTableName NVARCHAR(200)
 ,PrimaryKeyConstraintColumnName NVARCHAR(200)
 )

INSERT INTO fklist (
 ForeignKeyConstraintName
 ,ForeignKeyConstraintTableSchema
 ,ForeignKeyConstraintTableName
 ,ForeignKeyConstraintColumnName
 )
SELECT U.CONSTRAINT_NAME
 ,U.TABLE_SCHEMA
 ,U.TABLE_NAME
 ,U.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE fklist
SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE fklist
SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA
 ,PrimaryKeyConstraintTableName = TABLE_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE fklist
SET PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

SELECT *
FROM fklist

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
 FROM fklist
 WHERE RowId = @counter

 --exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')
 PRINT ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')

 SET @counter = @counter - 1
END

-----------------------------------------------------------------------------------

--Drop Foreign Keys









SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
 FROM fklist
 WHERE RowId = @counter

 --exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')
 PRINT ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')

 SET @counter = @counter - 1
END


--Recreate Foreign Keys
SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @constraint_col NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @pk_schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)
DECLARE @pk_table NVARCHAR(200)
DECLARE @pk_col NVARCHAR(200)

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
  ,@constraint_col = ForeignKeyConstraintColumnName
  ,@pk_schema = PrimaryKeyConstraintTableSchema
  ,@pk_table = PrimaryKeyConstraintTableName
  ,@pk_col = PrimaryKeyConstraintColumnName
 FROM fklist
 WHERE RowId = @counter

 PRINT ('ALTER TABLE [' + @schema + '].[' + @table + '] ADD CONSTRAINT ' + @constraint + ' FOREIGN KEY(' + @constraint_col + ') REFERENCES [' + @pk_schema + '].[' + @pk_table + '](' + @pk_col + ')')

 SET @counter = @counter - 1
END