Filling all your tables with test data VII

introduction

Hi guys in my last post  (Filling all your tables with test data) i explained how we can fill multiple tables by test data by using My own Stored procedure , but after i creating the script and i do on it more test on our staging environment and it’s  working very well i got  one issues on the script ( SP can not cover the tables with composite primary key )  at this time I reviewed  the Stored procedure from the scratch to know where is the issue and how i can solve it at the end i asked on e of my best friends Mohamed Abdel Kariem mentor T-SQL to help me in this subject and at the end we do awesome script and here the below new enhancement on our main stored procedure .

Stored procedure enhancement:

1. SP now supported tables with composite primary key
2. SP now supported tables with composite primary key with data type uniqueidentifier.
3. reduced the liens in the SP by using CASE technology
4. using QUOTENAME function instead of more concat.

 Stored procedure VII:

—Exec FillingTable_v2 ‘AC’,’TEST10′,’1000′
IF EXISTS ( SELECT *
FROM sys.objects
WHERE type = ‘P’
AND name = ‘FillingTable_v2’ )
DROP PROCEDURE FillingTable_v2
GO

CREATE PROC FillingTable_v2
@SchemaName NVARCHAR(100) = ‘dbo’ ,
@TableName NVARCHAR(MAX) = ”,
@batchCount NVARCHAR(200) = ‘0’
AS
BEGIN
— =============================================
— Author: Mustafa EL-Masry
— Technical Writer : Mohamed Abdel Kariem , Mustafa EL-Masry
— Technical Reviewer : https://mostafaelmasry.wordpress.com/ , http://sqlserver-performance-tuning.net/
— Create date: 13/02/2015
— VI POST :https://mostafaelmasry.wordpress.com/2015/02/13/filling-all-your-tables-with-test-data/
— VII POST :https://mostafaelmasry.wordpress.com/2015/02/15/filling-all-your-tables-with-test-data-vii/
— Description: Filling any Tables with any design with test data
— Notes: i suggested to drop foreign key before you Execute this SP
— New :SP now can cover the tables with composite primary key
— =============================================
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET DEADLOCK_PRIORITY HIGH

DECLARE @FullTableName AS NVARCHAR(200)= ”
DECLARE @PColumns AS NVARCHAR(MAX)
DECLARE @OtherColumns AS NVARCHAR(MAX)
DECLARE @PColumnsForCteRow1 AS NVARCHAR(MAX)
DECLARE @PColumnsForCteRow2 AS NVARCHAR(MAX)
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @TableName2 NVARCHAR(MAX)= ”
DECLARE @SchemaName2 NVARCHAR(100)= ”

SET @TableName2 = QUOTENAME(@TableName)
SET @SchemaName2 = QUOTENAME(@SchemaName)
SET @FullTableName = ( SELECT CONCAT(@SchemaName2, ‘.’, @TableName2)
)
SELECT @OtherColumns = COALESCE(@OtherColumns + ‘,’, ”)
+ QUOTENAME(c.name)
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@FullTableName)
AND c.is_computed = 0
AND c.name NOT IN (
SELECT Col.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab ,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.CONSTRAINT_NAME = Tab.CONSTRAINT_NAME
AND Col.TABLE_NAME = Tab.TABLE_NAME
AND CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND Col.TABLE_NAME = @TableName )
ORDER BY c.name
SELECT @PColumns = COALESCE(@PColumns + ‘,’, ”) + QUOTENAME(c.name)
+ ” ,
@PColumnsForCteRow1 = CASE WHEN ( t.name = ‘uniqueidentifier’ )
THEN COALESCE(@PColumnsForCteRow1
+ ‘,NEWID()’,
‘NEWID()’) + ‘ as ‘
+ QUOTENAME(c.name) + ”
ELSE COALESCE(@PColumnsForCteRow1
+ ‘,(select max(‘,
‘(select max(‘)
+ QUOTENAME(c.name)
+ ‘)+1 from ‘ + @FullTableName
+ ‘) as ‘ + QUOTENAME(c.name)
END ,
@PColumnsForCteRow2 = CASE WHEN ( t.name = ‘uniqueidentifier’ )
THEN COALESCE(@PColumnsForCteRow2
+ ‘,NEWID()’,
‘NEWID()’) + ‘ as [‘
+ c.name + ‘]’
ELSE COALESCE(@PColumnsForCteRow2
+ ‘,cte.’, ‘cte.’)
+ QUOTENAME(c.name) + ‘+1 as ‘
+ QUOTENAME(c.name)
END
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@FullTableName)
AND c.name IN (
SELECT Col.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab ,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.CONSTRAINT_NAME = Tab.CONSTRAINT_NAME
AND Col.TABLE_NAME = Tab.TABLE_NAME
AND CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND Col.TABLE_NAME = @TableName )
ORDER BY c.name
–print @PColumns
–print @OtherColumns
–print @PColumnsForCteRow1
–print @PColumnsForCteRow2

SET @SQL = ‘
BEGIN TRY
SET IDENTITY_INSERT ‘ + @FullTableName + ‘ ON;
END TRY
BEGIN CATCH
END CATCH
;
WITH cte AS
(
SELECT top 1 1 AS CteSerial, ‘ + @PColumnsForCteRow1 + ‘,’
+ @OtherColumns + ‘
FROM ‘ + @FullTableName + ‘
UNION ALL
— recur
SELECT cte.CteSerial + 1, ‘ + @PColumnsForCteRow2 + ‘,’
+ @OtherColumns + ‘
FROM cte
WHERE cte.CteSerial < ‘ + @batchCount + ‘
)
INSERT INTO ‘ + @FullTableName + ‘( ‘ + @PColumns + ‘,’
+ @OtherColumns + ‘)
select ‘ + @PColumns + ‘,’ + @OtherColumns + ‘ from cte
OPTION (MAXRECURSION 0);
BEGIN TRY
SET IDENTITY_INSERT ‘ + @FullTableName + ‘ OFF;
END TRY
BEGIN CATCH
END CATCH
; ‘
PRINT @SQL
EXEC sp_executesql @SQL
END