Introduction
Moving data and table schema with constraints in SQL server from one server to another server where the users are not allowed to restore the completed database, moving a change set (few table data, Primary keys, Foreign Keys and default values) from one server to other server is always challenging and required lot of efforts to prepare the SQL scripts.
In this article, I have created some queries that would help developers and makes their life easy to some extent.
Import and export data utility does not create any key and default constraints while creating tables in destination database.
Below are some useful queries that would help you..
Enable and Disable Identity Insert
This query will helps you to get the tables which have identity column in existing database and you can easily disable the identity insert values after running the output of the below query:
SELECT 'SET IDENTITY_INSERT ' + TABLE_NAME + ' ON'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME
Output
SET IDENTITY_INSERT Table1 ON
SET IDENTITY_INSERT Table3 ON
Once your identity insert is disabled, you can insert your data and enable the constraint back to normal through the below query:
SELECT 'SET IDENTITY_INSERT ' + TABLE_NAME + ' OFF'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME
Output
SET IDENTITY_INSERT Table1 OFF
SET IDENTITY_INSERT Table3 OFF
Primary Key Constraints
This query helps you to generate the query to create primary keys from existing database. Now it’s fun to create primary key on new database.
SELECT distinct 'ALTER TABLE ' + colinfo.Table_name + ' WITH CHECK ADD CONSTRAINT '
+ colinfo.CONSTRAINT_NAME +' PRIMARY KEY CLUSTERED ('
+ (SELECT stuff((select ',' + column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME =colinfo.CONSTRAINT_NAME FOR XML PATH('')),1,1,'')) + ')'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE colinfo inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblC on tblC.CONSTRAINT_NAME = colinfo.CONSTRAINT_NAME
and tblC.CONSTRAINT_SCHEMA = colinfo.CONSTRAINT_SCHEMA
WHERE tblC.CONSTRAINT_TYPE = 'PRIMARY KEY'
Note: Inner query gets the columns name of composite key.
Output
ALTER TABLE ActiveSubscriptions WITH CHECK ADD CONSTRAINT PK_ActiveSubscriptions PRIMARY KEY CLUSTERED (ActiveID)
ALTER TABLE CachePolicy WITH CHECK ADD CONSTRAINT PK_CachePolicy PRIMARY KEY CLUSTERED (CachePolicyID)
ALTER TABLE ChunkData WITH CHECK ADD CONSTRAINT PK_ChunkData PRIMARY KEY CLUSTERED (ChunkID)
ALTER TABLE ChunkSegmentMapping WITH CHECK ADD CONSTRAINT PK_ChunkSegmentMapping PRIMARY KEY CLUSTERED (ChunkId,SegmentId)
Unique Key Constraints
The above section was for the primary and below query will help you to get the unique key:
SELECT distinct 'ALTER TABLE ' + colinfo.CONSTRAINT_SCHEMA + '.' + colinfo.TABLE_NAME
+' ADD CONSTRAINT ' + colinfo.CONSTRAINT_NAME +' UNIQUE ( '
+(select stuff((select ',' + COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME =colinfo.CONSTRAINT_NAME FOR XML PATH('')),1,1,''))
+')'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE colinfo inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblC on tblC.CONSTRAINT_NAME = colinfo.CONSTRAINT_NAME
and tblC.CONSTRAINT_SCHEMA = colinfo.CONSTRAINT_SCHEMA
WHERE tblC.CONSTRAINT_TYPE = 'UNIQUE'
Output
ALTER TABLE dbo.Schedule ADD CONSTRAINT IX_Schedule UNIQUE ( Name,Path)
ALTER TABLE dbo.Contact ADD CONSTRAINT IX_Schedule UNIQUE ( ID)
Default Constraints
This query will retrieve the default constraints from database and generate query to create constraints.
SELECT 'ALTER TABLE ' + s.name +'.' + t.name +' ADD CONSTRAINT ' + d.name + ' default ' +
d.definition + ' FOR [' + c.name + ']'
FROM sys.default_constraints d
INNER JOIN sys.columns c ON d.parent_object_id = c.object_id
AND d.parent_column_id = c.column_id
INNER JOIN sys.tables t ON t.object_id = c.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
Output
ALTER TABLE dbo.DBUpgradeHistory ADD CONSTRAINT DF__DBUpgrade__DateT__75F77EB0 default (getdate()) FOR [DateTime]
ALTER TABLE dbo.Subscriptions ADD CONSTRAINT DF__Subscript__Repor__77DFC722 default ((0)) FOR [ReportZone]
ALTER TABLE dbo.Notifications ADD CONSTRAINT DF__Notificat__Repor__78D3EB5B default ((0)) FOR [ReportZone]
Foreign Key Constraints
This generates the query to create foreign key. If you would like to do this only for the selected tables, you can enhance this by adding where clause and table name is available in the ‘INFORMATION_SCHEMA.KEY_COLUMN_USAGE’.
Select distinct 'ALTER TABLE ' + ReferencingConstraint.TABLE_NAME +'
ADD CONSTRAINT ' + FK.CONSTRAINT_NAME + ' FOREIGN KEY (' +
(select stuff((select ',' + column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE colInfo
where colInfo.CONSTRAINT_NAME =FK.CONSTRAINT_NAME FOR XML PATH('')),1,1,''))
+')
REFERENCES ' + PK_cons.TABLE_NAME + ' ('+
(select stuff((select ',' + column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE colInfo
where colInfo.CONSTRAINT_NAME =FK.UNIQUE_CONSTRAINT_NAME FOR XML PATH('')),1,1,''))
+')'
+ ' ON DELETE ' + fk.UPDATE_RULE +
' ON UPDATE ' +FK.DELETE_RULE
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS FK
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencingConstraint
ON FK.CONSTRAINT_SCHEMA = ReferencingConstraint.CONSTRAINT_SCHEMA
AND FK.CONSTRAINT_NAME = ReferencingConstraint.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK_cons
ON FK.CONSTRAINT_SCHEMA = ReferencingConstraint.CONSTRAINT_SCHEMA
AND FK.UNIQUE_CONSTRAINT_NAME = PK_cons.CONSTRAINT_NAM
Output
ALTER TABLE ActiveSubscriptions ADD CONSTRAINT FK_ActiveSubscriptions_Subscriptions FOREIGN KEY (SubscriptionID)_
REFERENCES Subscriptions (SubscriptionID) ON DELETE NO ACTION ON UPDATE CASCADE
ALTER TABLE CachePolicy ADD CONSTRAINT FK_CachePolicyReportID FOREIGN KEY (ReportID)_
REFERENCES Catalog (ItemID) ON DELETE NO ACTION ON UPDATE CASCADE
ALTER TABLE Catalog ADD CONSTRAINT FK_Catalog_CreatedByID FOREIGN KEY (CreatedByID)_
REFERENCES Users (UserID) ON DELETE NO ACTION ON UPDATE NO ACTION
