CodeseniorSoftware Tutorials

Before using the script below, an empty database is created and then it scans all the tables in the other database and transfers them with their data. This way you can quickly generate a test database from a very large database.

Note: Don't forget to create the target database from source database script!

USE [TTBISDBTEST]
DECLARE @tableName NVARCHAR(128),
        @schemaName NVARCHAR(128),
        @sqlCmd NVARCHAR(MAX),
        @columnList NVARCHAR(MAX),
        @hasIdentity BIT,
        @databaseName NVARCHAR(255) = 'TTBISDB',
        @qualifiedTableName NVARCHAR(255),
        @destQualifiedTableName NVARCHAR(255);

-- Foreign key kısıtlamalarını devre dışı bırak
SET @sqlCmd = 'EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"';
EXEC sp_executesql @sqlCmd;

DECLARE tableCursor CURSOR FOR
SELECT t.name, s.name
FROM [TTBISDB].sys.tables AS t
JOIN [TTBISDB].sys.schemas AS s ON t.schema_id = s.schema_id
WHERE t.type = 'U' and t.name !='sysdiagrams' and s.name!='sm_log';

OPEN tableCursor;

FETCH NEXT FROM tableCursor INTO @tableName, @schemaName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Şu anki işlem tablosu: ' + @schemaName + '.' + @tableName;

    SET @qualifiedTableName = QUOTENAME(@databaseName) + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
    SET @destQualifiedTableName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);

    SELECT @columnList = STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ')
    FROM [TTBISDB].INFORMATION_SCHEMA.COLUMNS AS c
    WHERE c.TABLE_NAME = @tableName AND c.TABLE_SCHEMA = @schemaName;

    SELECT @hasIdentity = CASE 
        WHEN EXISTS (
            SELECT * 
            FROM [TTBISDB].sys.columns AS col
            WHERE col.object_id = OBJECT_ID(@qualifiedTableName, 'U') AND col.is_identity = 1
        ) THEN 1 ELSE 0 END;

    IF @hasIdentity = 1
    BEGIN
        SET @sqlCmd = 'SET IDENTITY_INSERT ' + @destQualifiedTableName + ' ON;' +
		              'DELETE FROM '+ @destQualifiedTableName+';'+
                      'INSERT INTO ' + @destQualifiedTableName + ' (' + @columnList + ') SELECT ' + @columnList + ' FROM ' + @qualifiedTableName + ';' +
                      'SET IDENTITY_INSERT ' + @destQualifiedTableName + ' OFF;';
        EXEC sp_executesql @sqlCmd;
    END
    ELSE
    BEGIN
        SET @sqlCmd = 'DELETE FROM '+ @destQualifiedTableName+';'+
					  'INSERT INTO ' + @destQualifiedTableName + ' (' + @columnList + ') SELECT ' + @columnList + ' FROM ' + @qualifiedTableName + ';';
        EXEC sp_executesql @sqlCmd;
    END

    FETCH NEXT FROM tableCursor INTO @tableName, @schemaName;
END;

CLOSE tableCursor;
DEALLOCATE tableCursor;

-- Foreign key kısıtlamalarını tekrar etkinleştir
SET @sqlCmd = 'EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"';
EXEC sp_executesql @sqlCmd;