Esse script server para imprimir o create de tabelas desejaveis de um banco.
Altere o SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE '%bkp%' AND TABLE_NAME NOT IN ('TABELA_FORA_1','TABELA_FORA_2','TABELA_FORA_3','TABELA_FORA_4'), conforme sua necessidade.
DECLARE @BASE VARCHAR(MAX),
@COLS VARCHAR(MAX),
@CREATE_SQL VARCHAR(MAX);
USE BANCO;
DECLARE BASES CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE '%bkp%'
AND TABLE_NAME NOT IN (
'TABELA_FORA_1',
'TABELA_FORA_2',
'TABELA_FORA_3',
'TABELA_FORA_4'
)
ORDER BY TABLE_NAME;
OPEN BASES;
FETCH NEXT FROM BASES INTO @BASE;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COLS = '';
DECLARE @COLUMN_NAME SYSNAME,
@DATA_TYPE NVARCHAR(128),
@CHAR_MAX_LEN INT,
@NUM_PRECISION INT,
@NUM_SCALE INT;
DECLARE COLS_CURSOR CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @BASE
ORDER BY ORDINAL_POSITION;
OPEN COLS_CURSOR;
FETCH NEXT FROM COLS_CURSOR INTO @COLUMN_NAME, @DATA_TYPE, @CHAR_MAX_LEN, @NUM_PRECISION, @NUM_SCALE;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COLS += CHAR(10) + ' [' + @COLUMN_NAME + '] ' + @DATA_TYPE;
IF @DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND @CHAR_MAX_LEN IS NOT NULL
BEGIN
SET @COLS += '(' + CASE WHEN @CHAR_MAX_LEN = -1 THEN 'MAX' ELSE CAST(@CHAR_MAX_LEN AS VARCHAR) END + ')';
END
ELSE IF @DATA_TYPE IN ('decimal', 'numeric') AND @NUM_PRECISION IS NOT NULL
BEGIN
SET @COLS += '(' + CAST(@NUM_PRECISION AS VARCHAR) + ',' + CAST(@NUM_SCALE AS VARCHAR) + ')';
END
SET @COLS += ',';
FETCH NEXT FROM COLS_CURSOR INTO @COLUMN_NAME, @DATA_TYPE, @CHAR_MAX_LEN, @NUM_PRECISION, @NUM_SCALE;
END
CLOSE COLS_CURSOR;
DEALLOCATE COLS_CURSOR;
-- Remove a última vírgula
IF RIGHT(@COLS, 1) = ','
SET @COLS = LEFT(@COLS, LEN(@COLS) - 1);
SET @CREATE_SQL = 'CREATE TABLE [' + @BASE + '] (' + @COLS + CHAR(10) + ');';
PRINT '------------------ Tabela: ' + @BASE + ' ------------------';
PRINT @CREATE_SQL;
PRINT '---------------------------------------------------------';
FETCH NEXT FROM BASES INTO @BASE;
END;
CLOSE BASES;
DEALLOCATE BASES;