One of our SQL enterprise servers has got 150+ game databases, eventually, the team has created a separate database (with exactly the same schema) for each game. Over time, the team end up having 150+ databases and the number is increasing for each month or quarter (at least one game arrives for each quarter that means ONE new DB arrives for each quarter). The team does not want to make any changes from the application side since many applications and reports are talking to this enterprise server, and, it is a huge effort to change the architecture.
come back and told to post a detailed article having a primary key, unique keys and Foreign Keys in my test tables in each test game databases, and they ought to move few COLD databases data into COMMON_GAME database, keeping HOT databases data AS-IS in the individual game databases. They are NOT ready to move to CLOUD from ON-PREM (though they are using SQL 2016 Ent Version) because of many reasons to implement SQL Server Stretch Database concept(to dynamically stretch warm and cold transactional data).
My thought process is to follow the below steps to showcase that it can be achieved
- Create test Game databases with test tables with Primary, unique keys and Foreign Keys and load sample data in test game databases.
- Create a sample game database
- Create sample tables with Primary keys, Foreign keys and Indexes
- Load the sample data into the sample tables.
- Clone one more game database with the same schema, RESEED the identity and load the data.
- Clone COMMON_GAME database from one of the sample game database.
- ADD GAME_NO column (to distinguish each database records) in all tables of COMMON_GAME database.
- Update the Primary key constraints in COMMON_GAME database except for Identity Keys.
- Update the Foreign key constraints in COMMON_GAME database.
- Update the Indexes in COMMON_GAME database.
- [Optional Step] Create Partition Schema and Partition Function on GAME_NO
- Load the data into COMMON_GAME database from all individual GAME_*** databases and rename the tables (*_Old) after loading the data.
- Verify the data in COMMON_GAME database and drop the *_Old tables from individual game databases.
- Create views and triggers in all the individual game databases.
- Validate the INSERT and UPDATE statements.
- Maintenance GOING FORWARD when the game database becomes COLD and ready to move to COMMON_GAME database.
Let's the do the DEMO
- Create test Game databases with test tables with Primary, unique keys and Foreign Keys and load sample data in test game databases.
Create a sample game database
USE [master]
GO
SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM SYS.sysdatabases WHERE name = 'GAME_001')
ALTER DATABASE Game_001 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE IF EXISTS Game_001
GO
CREATE DATABASE Game_001
GO
Create sample tables with Primary keys, Foreign keys and Indexes. I have added default constraints for most of the columns just to make life easier.
USE Game_001
GO
--Drop tables
DROP TABLE IF EXISTS [dbo].[Books]
DROP TABLE IF EXISTS [dbo].[USERS]
CREATE TABLE [dbo].[USERS](
[UserID] [bigint] NOT NULL IDENTITY(1,1) ,
[NationalNo] BIGINT NOT NULL,
[OwnerId] BIGINT NOT NULL,
[NAME] [nvarchar](50) NOT NULL,
[ActiveFlag] bit NOT NULL CONSTRAINT [DF_USERS_ActiveFlag] DEFAULT ((1)),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_USERS_rowguid] DEFAULT (newid()) ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_USERS_ModifiedDate] DEFAULT (getdate()) CONSTRAINT [CK_USERS_ModifiedDate] CHECK ([ModifiedDate] > GETDATE()-1),
CONSTRAINT [PK_USERS_USERID] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[USERS] ADD CONSTRAINT [UX_USERS_OwnerId] UNIQUE ([OwnerId])
GO
DROP TABLE IF EXISTS [dbo].[Books]
CREATE TABLE [dbo].[Books](
[BookNo] [BIGINT] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Owner] [bigint] NOT NULL CONSTRAINT [FK_Books_Users_UserId] FOREIGN KEY([Owner]) REFERENCES [dbo].[USERS] ([OwnerId]),
[FolderFlag] [bit] NOT NULL CONSTRAINT [DF_Books_FolderFlag] DEFAULT ((1)),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Books_rowguid] DEFAULT (newid()) ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_BOOKS_ModifiedDate] DEFAULT (getdate()) CONSTRAINT [CK_BOOKS_ModifiedDate] CHECK ([ModifiedDate] > GETDATE()-1),
CONSTRAINT [PK_Books_BookNo_Owner] PRIMARY KEY CLUSTERED
(
[BookNo] ASC,[Owner] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Books_rowguid] UNIQUE NONCLUSTERED
(
[rowguid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--LETS ADD FUNCTION TO ADD ONE UNIQUE INDEX, THIS IS JUST TO USE AS A DEFAULT CONSTRAINT ON [NationalNo] IN [dbo].[USERS] TABLE
DROP FUNCTION IF EXISTS [dbo].[ufnGetNationalOrBookNo]
GO
CREATE FUNCTION [dbo].[ufnGetNationalOrBookNo](@ID INT)
RETURNS BIGINT
AS
BEGIN
DECLARE @NationalNo BIGINT,@BookNo BIGINT,@ReturnNo BIGINT,@OwnerNo BIGINT;
SELECT @NationalNo = MIN([NationalNo])-1 FROM [dbo].[USERS]
SELECT @BookNo = MIN([BookNo])-1 FROM [dbo].[BOOKS]
SELECT @OwnerNo = MIN([OwnerId])-1 FROM [dbo].[USERS]
IF (@ID =1 ) SELECT @ReturnNo = ISNULL(@NationalNo,1111111111111111111)
IF (@ID =2 ) SELECT @ReturnNo = ISNULL(@BookNo,1111111111111111111)
IF (@ID =3 ) SELECT @ReturnNo = ISNULL(@OwnerNo,1111111111111111111)
RETURN @ReturnNo;
END;
GO
ALTER TABLE [dbo].[Books] CHECK CONSTRAINT [FK_Books_Users_UserId]
GO
ALTER TABLE [dbo].[USERS] ADD CONSTRAINT [DF_Users_NationalNo] DEFAULT (([dbo].[ufnGetNationalOrBookNo](1))) FOR [NationalNo]
GO
ALTER TABLE [dbo].[USERS] ADD CONSTRAINT [DF_Users_OwnerId] DEFAULT (([dbo].[ufnGetNationalOrBookNo](3))) FOR [OwnerId]
GO
ALTER TABLE [dbo].[Books] ADD CONSTRAINT [DF_Books_BookNo] DEFAULT (([dbo].[ufnGetNationalOrBookNo](2))) FOR [BookNo]
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Users_NationalNo_UserID] ON [dbo].[USERS] ( [NationalNo] ASC,[UserID] DESC)
GO
CREATE UNIQUE NONCLUSTERED INDEX [UX_Users_NationalNo] ON [dbo].[USERS] ( [NationalNo] ASC,[UserID] DESC) INCLUDE ([NAME],[ModifiedDate]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_USERS_NAME_OrganizationNode] ON [dbo].[USERS] ([NAME] DESC,[ActiveFlag] ASC ) INCLUDE ([ModifiedDate],[NationalNo]) ON [PRIMARY]
GO
Load the sample data into the sample tables.
USE Game_001
GO
INSERT [dbo].[USERS] ([NAME])
SELECT 'NARA GAME_001 USER RECORD'
GO 300
GO
INSERT [dbo].[BOOKS] ([Name],[Owner])
SELECT 'NARA GAME_001 BOOK RECORD',[OwnerId] FROM [dbo].[USERS]
GO
Clone one more game database with the same schema, RESEED the identity and load the data.
USE MASTER
GO
IF EXISTS (SELECT * FROM SYS.sysdatabases WHERE name = 'GAME_100')
ALTER DATABASE GAME_100 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE IF EXISTS GAME_100
GO
DBCC CLONEDATABASE(GAME_001,GAME_100)
GO
ALTER DATABASE [GAME_100] SET READ_WRITE WITH NO_WAIT
GO
USE GAME_100
GO
--RESEED IDENTITY COLUMNS AFTER CLONING THE DATABASE
IF(OBJECT_ID('tempdb..#ReseedIdentity') IS NOT NULL) DROP TABLE #ReseedIdentity
SELECT DISTINCT IDENT_SEED(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)) AS Seed,T.TABLE_SCHEMA,
IDENT_INCR(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)) AS Increment,T.TABLE_NAME,
COLUMNPROPERTY(OBJECT_ID(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)), C.COLUMN_NAME,'ISIDENTITY') AS Ident
INTO #ReseedIdentity
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)),C.COLUMN_NAME,'ISIDENTITY') = 1 AND
T.TABLE_TYPE = 'BASE TABLE'
WHILE EXISTS (SELECT * FROM #ReseedIdentity)
BEGIN
DECLARE @SQLCMD NVARCHAR(MAX),@TABLE_NAME SYSNAME,@TABLE_SCHEMA SYSNAME,@SEED INT
SELECT TOP 1 @TABLE_SCHEMA = TABLE_SCHEMA,@TABLE_NAME =TABLE_NAME,@SEED= SEED FROM #ReseedIdentity
SELECT @SQLCMD =' DBCC CHECKIDENT (''['+@TABLE_SCHEMA+'].['+@TABLE_NAME+']'', RESEED, '+CAST(@SEED-1 AS varchar)+')'
--PRINT @SQLCMD
EXEC(@SQLCMD)
DELETE #ReseedIdentity WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
END
GO
INSERT [dbo].[USERS] ([NAME])
SELECT 'NARA GAME_100 USER RECORD'
GO 500
GO
INSERT [dbo].[BOOKS] ([Name],[Owner])
SELECT 'NARA GAME_100 BOOK RECORD',[OwnerId] FROM [dbo].[USERS]
GO
Let's check the number of records in GAME_100 records
2. Clone COMMON_GAME database from one of the sample game database.
USE MASTER
GO
IF EXISTS (SELECT * FROM SYS.sysdatabases WHERE name = 'COMMON_GAME')
ALTER DATABASE COMMON_GAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE IF EXISTS COMMON_GAME
GO
DBCC CLONEDATABASE(GAME_001,COMMON_GAME)
GO
ALTER DATABASE [COMMON_GAME] SET READ_WRITE WITH NO_WAIT
GO
USE COMMON_GAME
GO
--RESEED IDENTITY COLUMNS AFTER CLONING THE DATABASE
IF(OBJECT_ID('tempdb..#ReseedIdentity') IS NOT NULL) DROP TABLE #ReseedIdentity
SELECT DISTINCT IDENT_SEED(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)) AS Seed,T.TABLE_SCHEMA,
IDENT_INCR(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)) AS Increment,T.TABLE_NAME,
COLUMNPROPERTY(OBJECT_ID(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)), C.COLUMN_NAME,'ISIDENTITY') AS Ident
INTO #ReseedIdentity
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)),C.COLUMN_NAME,'ISIDENTITY') = 1 AND
T.TABLE_TYPE = 'BASE TABLE'
WHILE EXISTS (SELECT * FROM #ReseedIdentity)
BEGIN
DECLARE @SQLCMD NVARCHAR(MAX),@TABLE_NAME SYSNAME,@TABLE_SCHEMA SYSNAME,@SEED INT
SELECT TOP 1 @TABLE_SCHEMA = TABLE_SCHEMA,@TABLE_NAME =TABLE_NAME,@SEED= SEED FROM #ReseedIdentity
SELECT @SQLCMD =' DBCC CHECKIDENT (''['+@TABLE_SCHEMA+'].['+@TABLE_NAME+']'', RESEED, '+CAST(@SEED-1 AS varchar)+')'
--PRINT @SQLCMD
EXEC(@SQLCMD)
DELETE #ReseedIdentity WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
END
GO
3. ADD GAME_NO column (to distinguish each database records) in all tables of COMMON_GAME database.
Please make sure to NOT to add GAME_NO column in all domain/master/lookup tables.
USE COMMON_GAME
GO
--ADD NEW COLUMNS IN ALL TABLES IN COMMON DATAABSE
IF (OBJECT_ID('tempdb..#alltables') IS NOT NULL) DROP TABLE #alltables
SELECT TABLE_SCHEMA,TABLE_NAME INTO #alltables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
WHILE EXISTS (SELECT * FROM #alltables)
BEGIN
DECLARE @TABLE_SCHEMA SYSNAME,@TABLE_NAME SYSNAME,@SQLCMD NVARCHAR(MAX)
SELECT TOP 1 @TABLE_SCHEMA= TABLE_SCHEMA, @TABLE_NAME = TABLE_NAME FROM #alltables
SELECT @SQLCMD = 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''GAME_NO'' AND TABLE_NAME = '''+@TABLE_NAME+''' AND TABLE_SCHEMA = '''+@TABLE_SCHEMA+''') ALTER TABLE ['+@TABLE_SCHEMA+'].['+@TABLE_NAME+'] ADD GAME_NO SMALLINT NOT NULL'
--PRINT @SQLCMD
EXEC SP_EXECUTESQL @SQLCMD
DELETE #alltables WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA
END
GO
4. Update the Primary key constraints in COMMON_GAME database except for Identity Keys.
I have made an assumption here that No Identity key is part of the foreign key.
USE COMMON_GAME
GO
IF(OBJECT_ID('tempdb..#PrimayKeyONIdentity') IS NOT NULL) DROP TABLE #PrimayKeyONIdentity
SELECT DISTINCT T.TABLE_CATALOG,T.TABLE_SCHEMA,T.TABLE_NAME,CTU.CONSTRAINT_NAME
INTO #PrimayKeyONIdentity
FROM COMMON_GAME.INFORMATION_SCHEMA.TABLES T
JOIN COMMON_GAME.INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_CATALOG = T.TABLE_CATALOG AND T.TABLE_NAME = C.TABLE_NAME AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
JOIN COMMON_GAME.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU ON CTU.TABLE_CATALOG = T.TABLE_CATALOG AND CTU.TABLE_SCHEMA = T.TABLE_SCHEMA
AND CTU.TABLE_NAME = T.TABLE_NAME
WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(T.TABLE_SCHEMA)+'.'+QUOTENAME(T.TABLE_NAME)),C.COLUMN_NAME,'ISIDENTITY') = 1 AND
T.TABLE_TYPE = 'BASE TABLE'IF(OBJECT_ID('tempdb..#PRIMARYKEY_CONSTRAINTS') IS NOT NULL) DROP TABLE #PRIMARYKEY_CONSTRAINTS
SELECT INDEX_NAME CONSTRAINT_NAME,
'IF EXISTS (SELECT * FROM COMMON_GAME.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE CONSTRAINT_NAME = '''+(INDEX_NAME)+''' AND TABLE_SCHEMA = '''+(TABLE_SCHEMA)+''' AND TABLE_NAME = '''+(TABLE_NAME)+''' AND TABLE_CATALOG =''COMMON_GAME'') ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+' DROP CONSTRAINT'+ QUOTENAME(INDEX_NAME) + CHAR(13)+
'ALTER TABLE '+ QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+ ' ADD CONSTRAINT ' + QUOTENAME(INDEX_NAME) + IS_UNIQUE_CONSTRAINT + IS_PRIMARY_KEY + INDEX_TYPE_DESC + '('+[INDEX_COLUMNS]+' ,[GAME_NO] DESC ) '
-- +case when len([INCLUDED_COLUMNS])>0 then CHAR(13) +'INCLUDE (' + [INCLUDED_COLUMNS]+ ')' else '' end + CHAR(13)+'WITH (' + INDEXOPTIONS+ ') ON ' + QUOTENAME(FILEGROUPNAME) + ';'
AS SQLCMD INTO #PRIMARYKEY_CONSTRAINTS
FROM (
SELECT DISTINCT SCHEMA_NAME(ST.SCHEMA_ID) TABLE_SCHEMA, ST.NAME TABLE_NAME, SIX.NAME INDEX_NAME,
CASE WHEN SIX.IS_UNIQUE = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE,
CASE WHEN SIX.IS_UNIQUE_CONSTRAINT = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE_CONSTRAINT
,CASE WHEN SIX.IS_PRIMARY_KEY = 1 THEN ' PRIMARY KEY ' ELSE '' END AS IS_PRIMARY_KEY
, SIX.TYPE_DESC COLLATE DATABASE_DEFAULT INDEX_TYPE_DESC,
CASE WHEN SIX.IS_PADDED=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END
+ CASE WHEN SIX.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END
+ CASE WHEN SIX.ALLOW_ROW_LOCKS=1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END
+ CASE WHEN INDEXPROPERTY(ST.OBJECT_ID, SIX.NAME, 'ISSTATISTICS') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END
+ CASE WHEN SIX.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END
--+ ', SORT_IN_TEMPDB = OFF '+ CASE WHEN IX.FILL_FACTOR > 0 THEN ',FILLFACTOR =' + CAST(IX.FILL_FACTOR AS VARCHAR(3)) ELSE ' ' END
AS INDEXOPTIONS,FILEGROUP_NAME(SIX.DATA_SPACE_ID) FILEGROUPNAME
,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME)+ ' '+ CASE WHEN IXC.IS_DESCENDING_KEY =1 THEN 'DESC' ELSE 'ASC' END AS 'data()'
FROM SYS.TABLES T
JOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_ID
JOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_ID
JOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID AND IXC.COLUMN_ID=COL.COLUMN_ID
WHERE IX.TYPE>0 AND (SIX.IS_PRIMARY_KEY=1 OR SIX.IS_UNIQUE_CONSTRAINT=1) AND IXC.IS_INCLUDED_COLUMN <> 1 AND COL.NAME<> 'GAME_NO'
AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAME
FOR XML PATH('')),'~~',', '), 1, 2, '') as [INDEX_COLUMNS]
--CASE WHEN LEN(INCLUDED_COLUMN) >1 THEN
--STUFF(REPLACE((SELECT '~~' + LTRIM(RTRIM(INCLUDED_COLUMN)) AS 'data()' FROM #INDEXCOLUMNS IC WHERE IC.INDEXNAME = I.INDEXNAME
--FOR XML PATH('')),'~~',', '), 1, 2, '') ELSE '' END as [INCLUDED_COLUMNS]
,ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME) AS 'data()' FROM SYS.TABLES T
JOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_ID
JOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_ID
JOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID AND IXC.COLUMN_ID=COL.COLUMN_ID
WHERE IX.TYPE>0 AND (SIX.IS_PRIMARY_KEY=1 OR SIX.IS_UNIQUE_CONSTRAINT=1) AND IXC.IS_INCLUDED_COLUMN = 1 AND COL.NAME<> 'GAME_NO'
AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAME
FOR XML PATH('')),'~~',', '), 1, 2, ''),'') as [INCLUDED_COLUMNS]
FROM SYS.TABLES ST INNER JOIN SYS.INDEXES SIX ON ST.OBJECT_ID=SIX.OBJECT_ID
WHERE SIX.TYPE>0 AND ( SIX.IS_PRIMARY_KEY=1 OR SIX.IS_UNIQUE_CONSTRAINT=1)
AND ST.IS_MS_SHIPPED=0 AND ST.NAME<>'SYSDIAGRAMS'
) A WHERE INDEX_NAME NOT IN (SELECT CONSTRAINT_NAME FROM #PrimayKeyONIdentity)
WHILE EXISTS (SELECT * FROM #PRIMARYKEY_CONSTRAINTS)
BEGIN
DECLARE @SQLCMD NVARCHAR(MAX),@CONSTRAINT_NAME VARCHAR(500)
SELECT TOP 1 @CONSTRAINT_NAME = CONSTRAINT_NAME ,@SQLCMD = SQLCMD FROM #PRIMARYKEY_CONSTRAINTS
PRINT @SQLCMD
EXEC(@SQLCMD)
DELETE #PRIMARYKEY_CONSTRAINTS WHERE CONSTRAINT_NAME = @CONSTRAINT_NAME
END
GO
5. Update the Foreign key constraints in COMMON_GAME database.
USE COMMON_GAME
GO
IF(OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL) DROP TABLE #ForeignKeys
SELECT CONSTRAINT_NAME,--UC_CONSTRAINT_NAME,UC_TABLE_SCHEMA,UC_TABLE_NAME,
'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = '''+'UX_'+REFERENCED_TABLE_NAME+'_'+REPLACE(REPLACE(REPLACE([REFERENCED_COLUMNS],',','_'),'[',''),']','')+'_GAME_NO'''+') ALTER TABLE '+QUOTENAME([REFERENCED_TABLE_SCHEMA])+'.'+QUOTENAME([REFERENCED_TABLE_NAME])+' ADD CONSTRAINT [UX_'+REFERENCED_TABLE_NAME+'_'+REPLACE(REPLACE(REPLACE([REFERENCED_COLUMNS],',','_'),'[',''),']','')+'_GAME_NO'+'] UNIQUE (' +[REFERENCED_COLUMNS]+',[GAME_NO] )'+
'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = ''' +CONSTRAINT_NAME +''') ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) + ' DROP CONSTRAINT ' +QUOTENAME(CONSTRAINT_NAME)+' ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+' WITH CHECK ADD CONSTRAINT '+QUOTENAME(CONSTRAINT_NAME)
+' FOREIGN KEY('+[COLUMNS]+',[GAME_NO] ) REFERENCES '
+ QUOTENAME([REFERENCED_TABLE_SCHEMA])+'.'+QUOTENAME([REFERENCED_TABLE_NAME]) + ' ( '+[REFERENCED_COLUMNS] + ',[GAME_NO] ) '+
CASE WHEN UC_CONSTRAINT_NAME IS NOT NULL AND UC_CONSTRAINT_NAME NOT LIKE '%_GAME_NO' THEN 'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = ''' +UC_CONSTRAINT_NAME +''') ALTER TABLE '+QUOTENAME(UC_TABLE_SCHEMA)+'.'+QUOTENAME(UC_TABLE_NAME) + ' DROP CONSTRAINT ' +QUOTENAME(UC_CONSTRAINT_NAME) ELSE '' END AS SQLCMD
INTO #ForeignKeys FROM (
SELECT DISTINCT TC.CONSTRAINT_NAME,TC.TABLE_SCHEMA,TC.TABLE_NAME [TABLE_NAME],TC2.TABLE_SCHEMA AS [REFERENCED_TABLE_SCHEMA],TC2.TABLE_NAME [REFERENCED_TABLE_NAME]
,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COLUMN_NAME) AS 'data()' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE IC
WHERE IC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND COLUMN_NAME<> 'GAME_NO' ORDER BY ORDINAL_POSITION
FOR XML PATH('')),'~~',', '), 1, 2, '') as [COLUMNS]
,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COLUMN_NAME) AS 'data()' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE IC
WHERE IC.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME AND COLUMN_NAME<> 'GAME_NO' ORDER BY ORDINAL_POSITION
FOR XML PATH('')),'~~',', '), 1, 2, '') as [REFERENCED_COLUMNS],
UC.CONSTRAINT_NAME AS UC_CONSTRAINT_NAME,UC.TABLE_SCHEMA UC_TABLE_SCHEMA,UC.TABLE_NAME UC_TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON TC.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON RC.UNIQUE_CONSTRAINT_SCHEMA = TC2.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = TC2.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON TC2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA AND TC2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS UC ON UC.TABLE_SCHEMA = TC2.TABLE_SCHEMA AND UC.TABLE_NAME =TC2.TABLE_NAME
WHERE TC.CONSTRAINT_TYPE ='FOREIGN KEY' AND UC.CONSTRAINT_TYPE ='UNIQUE'
) A
WHILE EXISTS (SELECT * FROM #ForeignKeys)
BEGIN
DECLARE @CONSTRAINT_NAME VARCHAR(500),@SQLCMD NVARCHAR(MAX)
SELECT TOP 1 @CONSTRAINT_NAME = CONSTRAINT_NAME,@SQLCMD = SQLCMD FROM #ForeignKeys
PRINT @SQLCMD
EXEC (@SQLCMD)
DELETE #ForeignKeys WHERE CONSTRAINT_NAME = @CONSTRAINT_NAME
END
GO
6. Update the Indexes in COMMON_GAME database.
USE COMMON_GAME
GO
IF(OBJECT_ID('tempdb..#INDEX_SCRIPT') IS NOT NULL) DROP TABLE #INDEX_SCRIPT
SELECT INDEX_NAME,'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = '''+INDEX_NAME+''') DROP INDEX '+QUOTENAME(INDEX_NAME)+' ON ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+
' CREATE '+ IS_UNIQUE +INDEX_TYPE_DESC + ' INDEX ' +QUOTENAME(INDEX_NAME)+' ON ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+ '('+[INDEX_COLUMNS]+' , [GAME_NO] DESC ) '+
case when [INCLUDED_COLUMNS]<>'' then CHAR(13) +'INCLUDE (' + [INCLUDED_COLUMNS]+ ')' else '' end + CHAR(13)+'WITH (' + INDEXOPTIONS+ ') ON ' + QUOTENAME(FILEGROUPNAME) + ';' SQLCMD
INTO #INDEX_SCRIPT FROM (
SELECT DISTINCT SCHEMA_NAME(ST.SCHEMA_ID) TABLE_SCHEMA, ST.NAME TABLE_NAME, SIX.NAME INDEX_NAME,
CASE WHEN SIX.IS_UNIQUE = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE
, SIX.TYPE_DESC COLLATE DATABASE_DEFAULT INDEX_TYPE_DESC,
CASE WHEN SIX.IS_PADDED=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END
+ CASE WHEN SIX.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END
+ CASE WHEN SIX.ALLOW_ROW_LOCKS=1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END
+ CASE WHEN INDEXPROPERTY(ST.OBJECT_ID, SIX.NAME, 'ISSTATISTICS') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END
+ CASE WHEN SIX.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END
--+ ', SORT_IN_TEMPDB = OFF '+ CASE WHEN IX.FILL_FACTOR > 0 THEN ',FILLFACTOR =' + CAST(IX.FILL_FACTOR AS VARCHAR(3)) ELSE ' ' END
AS INDEXOPTIONS,FILEGROUP_NAME(SIX.DATA_SPACE_ID) FILEGROUPNAME
,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME)+ ' '+ CASE WHEN IXC.IS_DESCENDING_KEY =1 THEN 'DESC' ELSE 'ASC' END AS 'data()'
FROM SYS.TABLES T
JOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_ID
JOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_ID
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=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN <> 1 AND COL.NAME<> 'GAME_NO'
AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAME
FOR XML PATH('')),'~~',', '), 1, 2, '') as [INDEX_COLUMNS]
,ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME) AS 'data()' FROM SYS.TABLES T
JOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_ID
JOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_ID
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=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN = 1 AND COL.NAME<> 'GAME_NO'
AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAME
FOR XML PATH('')),'~~',', '), 1, 2, ''),'') as [INCLUDED_COLUMNS]
FROM SYS.TABLES ST INNER JOIN SYS.INDEXES SIX ON ST.OBJECT_ID=SIX.OBJECT_ID
WHERE SIX.TYPE>0 AND ( SIX.IS_PRIMARY_KEY=0 AND SIX.IS_UNIQUE_CONSTRAINT=0)
AND ST.IS_MS_SHIPPED=0 AND ST.NAME<>'SYSDIAGRAMS' ) A
WHILE EXISTS (SELECT * FROM #INDEX_SCRIPT)
BEGIN
DECLARE @SQLCMD NVARCHAR(MAX),@INDEX_NAME VARCHAR(500)
SELECT TOP 1 @INDEX_NAME = INDEX_NAME ,@SQLCMD = SQLCMD FROM #INDEX_SCRIPT
PRINT @SQLCMD
EXEC(@SQLCMD)
DELETE #INDEX_SCRIPT WHERE INDEX_NAME = @INDEX_NAME
END
GO
7. [Optional Step] Create Partition Schema and Partition Function on GAME_NO
Since I have created GAME_100 that means there are 100 databases are becoming cold and will have to move the data to COMMON_GAME database. Here, I am creating 100 partition functions and you can also attach 100 file groups while creating a Partition Scheme.
USE [COMMON_GAME]
GO
IF NOT EXISTS (SELECT * FROM SYS.PARTITION_FUNCTIONS WHERE NAME = 'PF_GAME_NO')
CREATE PARTITION FUNCTION [PF_GAME_NO](SMALLINT) AS RANGE LEFT FOR VALUES
( N'1', N'2', N'3', N'4' ,N'5' ,N'6',N'7',N'8',N'9',N'10' ,
N'11', N'12', N'13', N'14',N'15',N'16',N'17',N'18',N'19',N'20' ,
N'21', N'22', N'23', N'24',N'25',N'26',N'27',N'28',N'29',N'30' ,
N'31', N'32', N'33', N'34',N'35',N'36',N'37',N'38',N'39',N'40' ,
N'41', N'42', N'43', N'44',N'45',N'46',N'47',N'48',N'49',N'50' ,
N'51', N'52', N'53', N'54',N'55',N'56',N'57',N'58',N'59',N'60' ,
N'61', N'62', N'63', N'64',N'65',N'66',N'67',N'68',N'69',N'70' ,
N'71', N'72', N'73', N'74',N'75',N'76',N'77',N'78',N'79',N'80' ,
N'81', N'82', N'83', N'84',N'85',N'86',N'87',N'88',N'89',N'90' ,
N'91', N'92', N'93', N'94',N'95',N'96',N'97',N'98',N'99',N'100' )
GO
IF NOT EXISTS (SELECT * FROM SYS.PARTITION_SCHEMES WHERE NAME = 'PS_GAME_NO')
CREATE PARTITION SCHEME [PS_GAME_NO] AS PARTITION [PF_GAME_NO] ALL TO ([PRIMARY])
GO
IF (OBJECT_ID('tempdb..#alltables') IS NOT NULL) DROP TABLE #alltables
SELECT TABLE_SCHEMA,TABLE_NAME INTO #alltables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
WHILE EXISTS (SELECT * FROM #alltables)
BEGIN
DECLARE @TABLE_SCHEMA SYSNAME,@TABLE_NAME SYSNAME,@SQLCMD NVARCHAR(MAX)
SELECT TOP 1 @TABLE_SCHEMA= TABLE_SCHEMA, @TABLE_NAME = TABLE_NAME FROM #alltables
SELECT @SQLCMD = 'IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = ''IX_'+@TABLE_NAME+'_GAME_NO'') CREATE NONCLUSTERED INDEX [IX_'+@TABLE_NAME+'_GAME_NO] ON '+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME)+' (GAME_NO) ON PS_GAME_NO(GAME_NO)'
PRINT @SQLCMD
EXEC SP_EXECUTESQL @SQLCMD
DELETE #alltables WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA
END
GO
8. Load the data into COMMON_GAME database from all individual GAME_*** databases and rename the tables (*_Old) after loading the data.
If the table is domain/master/lookup table which has the same data in all databases. You can copy that table data manually. Add the following filter after highlighted green " AND TABLE_NAME=''<TABLE_NAME>'' ".
Please make sure to update/add/replace database name in the highlighted filter before executing the query.
--COPY DATA FROM ALL GAMES INTO COMMON DATABASE AND AND RENAME THE TABLE (AS A BACKUP) IN INDIVIDUAL GAME DATABASES
USE COMMON_GAME
GO
IF (OBJECT_ID('tempdb..#allgames') IS NOT NULL) DROP TABLE #allgames
SELECT name into #allgames FROM SYS.sysdatabases WHERE name IN ('GAME_001','GAME_100')
WHILE EXISTS (SELECT * FROM #allgames)
BEGIN
DECLARE @DatabaseName SYSNAME,@TABLE_CATALOG SYSNAME,@TABLE_SCHEMA SYSNAME,@TABLE_NAME SYSNAME,@COLUMNS_LIST NVARCHAR(MAX)
SELECT top 1 @DatabaseName = name FROM #allgames
DECLARE @SQLTableCommnd NVARCHAR(MAX)
IF (OBJECT_ID('tempdb..##tableInfo') IS NOT NULL) DROP TABLE ##tableInfo
SET @SQLTableCommnd = 'SELECT DISTINCT T.TABLE_CATALOG,T.TABLE_SCHEMA,T.TABLE_NAME, STUFF(REPLACE((SELECT ''~~'' + QUOTENAME(SC.COLUMN_NAME) AS ''data()'' FROM ['+@DatabaseName+']. INFORMATION_SCHEMA.COLUMNS SC WHERE SC.TABLE_SCHEMA =C.TABLE_SCHEMA AND SC.TABLE_NAME=C.TABLE_NAME AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(SC.TABLE_SCHEMA)+''.''+QUOTENAME(SC.TABLE_NAME)),SC.COLUMN_NAME,''ISIDENTITY'') <> 1 AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(SC.TABLE_SCHEMA)+''.''+QUOTENAME(SC.TABLE_NAME)),SC.COLUMN_NAME,''IsComputed'') <> 1 AND SC.COLUMN_NAME<> ''GAME_NO'' FOR XML PATH('''')),''~~'','', ''), 1, 2, '''') as [COLUMNS_LIST], ISNULL(DEPENDENCY_LEVEL,0) AS DEPENDENCY_LEVEL INTO ##tableInfo FROM ['+@DatabaseName+']. INFORMATION_SCHEMA.TABLES T JOIN ['+@DatabaseName+']. INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME LEFT JOIN (SELECT TC.TABLE_SCHEMA,TC.TABLE_NAME [TABLE_NAME],999 DEPENDENCY_LEVEL FROM ['+@DatabaseName+']. INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN ['+@DatabaseName+']. INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME JOIN ['+@DatabaseName+']. INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON TC.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE = ''FOREIGN KEY'') FK ON FK.TABLE_SCHEMA = T.TABLE_SCHEMA AND FK.TABLE_NAME = T.TABLE_NAME WHERE TABLE_TYPE=''BASE TABLE'' ORDER BY ISNULL(DEPENDENCY_LEVEL,0)'
--PRINT @SQLTableCommnd
EXEC SP_EXECUTESQL @SQLTableCommnd
WHILE EXISTS (SELECT * FROM ##tableInfo )
BEGIN
SELECT TOP 1 @TABLE_CATALOG =TABLE_CATALOG ,@TABLE_SCHEMA=TABLE_SCHEMA,@TABLE_NAME=TABLE_NAME,@COLUMNS_LIST = COLUMNS_LIST FROM ##tableInfo ORDER BY DEPENDENCY_LEVEL
SET @SQLTableCommnd = 'INSERT [COMMON_GAME].['+@TABLE_SCHEMA+'].['+@TABLE_NAME+'] ('+@COLUMNS_LIST+',[GAME_NO]) SELECT '+@COLUMNS_LIST+','
+CONVERT(VARCHAR,CAST(SUBSTRING(@TABLE_CATALOG,6,LEN(@TABLE_CATALOG)) AS INT))+' FROM ['+@TABLE_CATALOG+'].['+@TABLE_SCHEMA+'].['+@TABLE_NAME+'] (NOLOCK)'
PRINT @SQLTableCommnd
EXEC SP_EXECUTESQL @SQLTableCommnd
SET @SQLTableCommnd = @TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+'SP_RENAME '''+@TABLE_NAME+''', '''+@TABLE_NAME+'_old'''
PRINT @SQLTableCommnd
EXEC (@SQLTableCommnd)
DELETE ##tableInfo WHERE TABLE_CATALOG= @TABLE_CATALOG AND TABLE_SCHEMA= @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
END
DELETE #allgames WHERE name=@DatabaseName
END
GO
9. Verify the data in COMMON_GAME database and drop the *_Old tables from individual game databases.
select count(*) from GAME_001.dbo.[USERS_Old]
select count(*) from GAME_001.dbo.[Books_Old]
select count(*) from GAME_100.dbo.[USERS_Old]
select count(*) from GAME_100.dbo.[Books_Old]
select count(*) from COMMON_GAME.dbo.[USERS]
select count(*) from COMMON_GAME.dbo.[Books]
Drop the "*_old" tables from individual game databases. try to drop the tables based on dependency order or try executing multiple times to drop dependent tables after getting the error.
USE master
GO
IF (OBJECT_ID('tempdb..#allgames') IS NOT NULL) DROP TABLE #allgames
SELECT name into #allgames FROM SYS.sysdatabases WHERE name IN ('GAME_001','GAME_100')
WHILE EXISTS (SELECT * FROM #allgames)
BEGIN
DECLARE @DatabaseName SYSNAME,@TABLE_CATALOG SYSNAME,@TABLE_SCHEMA SYSNAME,@TABLE_NAME SYSNAME
SELECT top 1 @DatabaseName = name FROM #allgames
DECLARE @SQLTableCommnd NVARCHAR(MAX)
IF (OBJECT_ID('tempdb..##tableInfo') IS NOT NULL) DROP TABLE ##tableInfo
SET @SQLTableCommnd = 'SELECT * INTO ##tableInfo FROM ['+@DatabaseName+']. INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'''
EXEC SP_EXECUTESQL @SQLTableCommnd
WHILE EXISTS (SELECT * FROM ##tableInfo)
BEGIN
SELECT TOP 1 @TABLE_CATALOG =TABLE_CATALOG ,@TABLE_SCHEMA=TABLE_SCHEMA,@TABLE_NAME=TABLE_NAME FROM ##tableInfo
SET @SQLTableCommnd = 'DROP TABLE '+QUOTENAME(@TABLE_CATALOG)+'.'+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME)
PRINT @SQLTableCommnd
EXEC SP_EXECUTESQL @SQLTableCommnd
DELETE ##tableInfo WHERE TABLE_CATALOG= @TABLE_CATALOG AND TABLE_SCHEMA= @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
END
DELETE #allgames WHERE name=@DatabaseName
END
GO
10.Create views and triggers in all the individual game databases.
Below script would create views (NOT included identity keys in columns) and INSTEAD OF INSERT trigger. Please make sure NOT to add GAME_NO column for all domain/master/lookup view (role-playing dimensions) or try creating the domain/master/lookup views manually.
--CREATE VIEWS IN INDVIDUAL GAME DATABASES
USE COMMON_GAME
GO
IF (OBJECT_ID('tempdb..#VIEWINFO') IS NOT NULL) DROP TABLE #VIEWINFO
SELECT DISTINCT T.TABLE_CATALOG,T.TABLE_SCHEMA,T.TABLE_NAME,
STUFF(REPLACE((SELECT '~~' + QUOTENAME(SC.COLUMN_NAME) AS 'data()'
FROM INFORMATION_SCHEMA.COLUMNS SC
WHERE SC.TABLE_SCHEMA =C.TABLE_SCHEMA AND SC.TABLE_NAME=C.TABLE_NAME AND SC.COLUMN_NAME<> 'GAME_NO' AND
COLUMNPROPERTY(OBJECT_ID(QUOTENAME(SC.TABLE_SCHEMA)+'.'+QUOTENAME(SC.TABLE_NAME)),SC.COLUMN_NAME,'ISIDENTITY') <> 1
ORDER BY SC.ORDINAL_POSITION
FOR XML PATH('')),'~~',', '), 1, 2, '') as [COLUMNS_LIST] INTO #VIEWINFO
FROM INFORMATION_SCHEMA.TABLES T JOIN
INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
IF (OBJECT_ID('tempdb..#allgamedbs') IS NOT NULL) DROP TABLE #allgamedbs
SELECT name AS DBNAME, VF.* INTO #allgamedbs FROM SYS.sysdatabases CROSS APPLY #VIEWINFO VF WHERE name IN ('GAME_001','GAME_100')
WHILE EXISTS (SELECT * FROM #allgamedbs)
BEGIN
DECLARE @DatabaseName SYSNAME,@TABLE_CATALOG SYSNAME,@TABLE_SCHEMA SYSNAME,@TABLE_NAME SYSNAME,@COLUMNS_LIST NVARCHAR(MAX),@SQLCMD NVARCHAR(MAX),@SQLVIEW NVARCHAR(MAX)
SELECT top 1 @DatabaseName = DBNAME,@TABLE_CATALOG =TABLE_CATALOG,@TABLE_SCHEMA=TABLE_SCHEMA,
@TABLE_NAME=TABLE_NAME,@COLUMNS_LIST = COLUMNS_LIST FROM #allgamedbs
--DROP VIEW IF EXITS
SET @SQLCMD = 'IF EXISTS (SELECT * FROM '+QUOTENAME(@DatabaseName)+'.INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME ='''''+@TABLE_NAME+''''' AND TABLE_SCHEMA = '''''+@TABLE_SCHEMA+''''') DROP VIEW '+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME)
SET @SQLVIEW =' EXEC ' +@DatabaseName+'.'+'SYS.'+'SP_EXECUTESQL @STMT= N''' + @SQLCMD +''''
--PRINT @SQLVIEW
EXEC (@SQLVIEW)
--CREATE VIEW
SET @SQLCMD ='CREATE VIEW '+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME) +' AS SELECT '+@COLUMNS_LIST +' FROM ' +QUOTENAME(@TABLE_CATALOG)+'.'+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME) + ' (NOLOCK) WHERE GAME_NO= '+ CONVERT(VARCHAR,CAST(SUBSTRING(@DatabaseName,6,LEN(@DatabaseName)) AS INT)) + ''
SET @SQLVIEW =' EXEC ' +@DatabaseName+'.'+'SYS.'+'SP_EXECUTESQL @STMT= N''' + @SQLCMD +''''
--PRINT @SQLVIEW
EXEC (@SQLVIEW)
--DROP TRIGGER IF EXISTS
SET @SQLCMD = 'IF EXISTS (SELECT * FROM '+QUOTENAME(@DatabaseName)+'.SYS.TRIGGERS WHERE NAME ='''''+'TRG_'+@TABLE_NAME+''''' ) DROP TRIGGER '+QUOTENAME(@TABLE_SCHEMA)+'.[TRG_'+(@TABLE_NAME)+']'
SET @SQLVIEW =' EXEC ' +@DatabaseName+'.'+'SYS.'+'SP_EXECUTESQL @STMT= N''' + @SQLCMD +''''
--PRINT @SQLVIEW
EXEC (@SQLVIEW)
--CREATE TRIGGER
SET @SQLCMD ='CREATE TRIGGER '+QUOTENAME(@TABLE_SCHEMA)+'.[TRG_'+(@TABLE_NAME) +'] ON '+ QUOTENAME(@DatabaseName)+'.'+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME)+' INSTEAD OF INSERT AS BEGIN INSERT INTO '+QUOTENAME(@TABLE_CATALOG)+'.'+QUOTENAME(@TABLE_SCHEMA)+'.'+QUOTENAME(@TABLE_NAME) +' ( '+@COLUMNS_LIST +',[GAME_NO]) SELECT '+@COLUMNS_LIST +',CAST(SUBSTRING(DB_NAME(),6,LEN(DB_NAME())) AS SMALLINT) FROM INSERTED END'
SET @SQLVIEW =' EXEC ' +@DatabaseName+'.'+'SYS.'+'SP_EXECUTESQL @STMT= N''' + @SQLCMD +''''
--PRINT @SQLVIEW
EXEC (@SQLVIEW)
DELETE #allgamedbs WHERE DBNAME =@DatabaseName AND TABLE_SCHEMA =@TABLE_SCHEMA AND TABLE_NAME=@TABLE_NAME
END
GO
11. Validate the INSERT and UPDATE statements.
Insert Validation:
SELECT COUNT(*) ROW_CNT_BEFORE FROM COMMON_GAME.DBO.[USERS] WHERE GAME_NO = 1
USE [Game_001]
GO
SELECT COUNT(*) ROW_CNT_BEFORE_INSERT FROM [dbo].[USERS]
GO
INSERT [dbo].[USERS] ([NationalNo],[OwnerId],[NAME],[ActiveFlag],[rowguid],[ModifiedDate])
SELECT '1111111111111110811','1111111111111110811', 'NARA GAME_001 USER RECORD INSERTING FROM VIEW',1, NEWID(),GETDATE()
GO
SELECT COUNT(*) ROW_CNT_AFTER_INSERT FROM [dbo].[USERS]
GO
SELECT COUNT(*) ROW_CNT_AFTER FROM COMMON_GAME.DBO.[USERS] WHERE GAME_NO = 1
Try Insert record in Books Table
SELECT COUNT(*) ROW_CNT_BEFORE FROM COMMON_GAME.DBO.[Books] WHERE GAME_NO = 1
USE [Game_001]
GO
INSERT [dbo].[Books] ([BookNo],[Name],[Owner],[FolderFlag],[rowguid],[ModifiedDate])
SELECT '1111111111111111111','NARA GAME_100 USER RECORD INSERTING FROM VIEW','1111111111111110811',1,NEWID(),GETDATE()
GO
SELECT COUNT(*) ROW_CNT_AFTER_INSERT FROM [dbo].[Books]
GO
SELECT COUNT(*) ROW_CNT_AFTER FROM COMMON_GAME.DBO.[Books] WHERE GAME_NO = 1
Insert records in GAME_100 database
SELECT COUNT(*) ROW_CNT_BEFORE FROM COMMON_GAME.DBO.[USERS] WHERE GAME_NO = 1
USE [Game_100]
GO
SELECT COUNT(*) ROW_CNT_BEFORE_INSERT FROM [dbo].[USERS]
GO
INSERT [dbo].[USERS] ([NationalNo],[OwnerId],[NAME],[ActiveFlag],[rowguid],[ModifiedDate])
SELECT '1111111111111110611','1111111111111110611', 'NARA GAME_100 USER RECORD INSERTING FROM VIEW',1, NEWID(),GETDATE()
GO
SELECT COUNT(*) ROW_CNT_AFTER_INSERT FROM [dbo].[USERS]
GO
SELECT COUNT(*) ROW_CNT_AFTER FROM COMMON_GAME.DBO.[USERS] WHERE GAME_NO = 1
SELECT COUNT(*) ROW_CNT_BEFORE FROM COMMON_GAME.DBO.[USERS] WHERE GAME_NO = 100
USE [Game_100]
GO
SELECT COUNT(*) ROW_CNT_BEFORE_INSERT FROM [dbo].[USERS]
GO
INSERT [dbo].[USERS] ([NationalNo],[OwnerId],[NAME],[ActiveFlag],[rowguid],[ModifiedDate])
SELECT '1111111111111110611','1111111111111110611', 'NARA GAME_100 USER RECORD INSERTING FROM VIEW',1, NEWID(),GETDATE()
GO
SELECT COUNT(*) ROW_CNT_AFTER_INSERT FROM [dbo].[USERS]
GO
SELECT COUNT(*) ROW_CNT_AFTER FROM COMMON_GAME.DBO.[USERS] WHERE GAME_NO = 100
GO
SELECT COUNT(*) ROW_CNT_BEFORE FROM COMMON_GAME.DBO.[Books] WHERE GAME_NO = 100
USE [Game_100]
GO
INSERT [dbo].[Books] ([BookNo],[Name],[Owner],[FolderFlag],[rowguid],[ModifiedDate])
SELECT '1111111111111111111','NARA GAME_100 USER RECORD INSERTING FROM VIEW','1111111111111110611',1,NEWID(),GETDATE()
GO
SELECT COUNT(*) ROW_CNT_AFTER_INSERT FROM [dbo].[Books]
GO
SELECT COUNT(*) ROW_CNT_AFTER FROM COMMON_GAME.DBO.[Books] WHERE GAME_NO = 100
Update Validation:
USE GAME_100
GO
UPDATE GAME_100.DBO.Books SET [Name] = 'UPDATING GAME_100 VALUE TO NARA'
SELECT @@ROWCOUNT
GO
SELECT COUNT(*) GAME_1_ROWS FROM COMMON_GAME.DBO.Books WHERE [GAME_NO] = '100' AND [Name] = 'UPDATING GAME_100 VALUE TO NARA'
SELECT COUNT(*) AS TOTAL_ROWS FROM COMMON_GAME.DBO.Books
GO
Achieved?. Let me know if you think otherwise
12. Maintenance GOING FORWARD when the game database becomes COLD and ready to move to COMMON_GAME database.
Let's say that
GAME_101 database becomes COLD and ready to move to COMMON_GAME database. Please follow the below steps in order to make GAME_101 cold.
[Optional Step] Create Additional partition.
USE COMMON_GAME
GO
ALTER PARTITION SCHEME [PS_GAME_NO]
NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION [PF_GAME_NO]() SPLIT RANGE(N'101')
Follow the below steps in order as explained above by replacing the "WHERE name IN ('GAME_001','GAME_100')" to "WHERE name IN ('GAME_101')"
8. Load the data into COMMON_GAME database from all individual GAME_*** databases and rename the tables (*_Old) after loading the data.
9. Verify the data in COMMON_GAME database and drop the *_Old tables from individual game databases.
10. Create views and triggers in all the individual game databases.
Achieved?. Let me know if you think otherwise
Please feel free to use any of the above scripts in case if suits for your scenarios. Let me know if you find anything wrong in the script/post. Mistakes can happen 😊 .
CONCLUSION:
By following this approach, you would get below benefits
- Store the domain/master/lookup tables data in a single place, which can be used by all COLD databases (Role Playing Dimensions).
- Can automate moving the databases to COLD based on certain conditions( maybe after 1 year or 2 years) by using the same scripts in a Job.
- Users, Reports or Applications would not be having any impact as they access AS-IS to the enterprise server.
- Can keep all COLD databases in a separate SQL INSTANCE and update the views to point to other SQL INSTANCE by creating a linked server.
Thanks
