SQL Script to generate Code for a table, with columns

Transact-SQL
1

I got so far on coding and got to read one table and then recreate this table as another name.
The end result is the table is identical to the one i am copying but i am adding a column of EXTRACT_ID to the table.
There will be over 20 tables so i wanted to recreate them all dynamically.

I am not sure how to put the code for the varchar NOT NULL, aswell.

Declare @StartTable sysname,
@ExtractTable sysname,
@TargetExtractTable sysname,
@sourceTable sysname, @DBSource sysname,
@SQLInsertString NVARCHAR(max),
@SQLCreateExtractTable NVARCHAR(max),
@TrackerTablePrimaryColumn NVARCHAR(MAX),
@SQLLine1 NVARCHAR(max),
@SQLLine1A NVARCHAR(max),
@SQLLine2 NVARCHAR(max),
@SQLLine3 NVARCHAR(max),
@SQLLine4 NVARCHAR(max),
@SQLLine5 NVARCHAR(max),
@SQLLine6 NVARCHAR(max),
@SQLLine7 NVARCHAR(max),
@SQLLine8 NVARCHAR(max),
@SQLLine5A NVARCHAR(max),
@SQLLine5B NVARCHAR(max),
@columnnames as varchar(max),
@StartTableName sysname
Select @StartTable='DBO.EMPLOYEES' , ----This can be any table i just want to mirror it.
@columnnames='['
Select @StartTableName = 'EMPLOYEES'
select @ExtractTable = 'NEW_EMPLOYEES'
select @DBSource = 'NEW_SOURCE'
select @SourceTable = 'DBO.EMPLOYEES'

Select @columnnames=@columnnames+sc.name+'],['
from sys.all_columns sc
where sc.object_id=object_ID(@SourceTable)
ORDER BY column_id
select @columnnames=left(@columnnames,len(@columnnames)-2) --get rid of the open bracket at the end

---Only Add table if not exist recreate table
select @SQLLine1A = 'if not exists (select * from sysobjects where name=' + @ExtractTable + ' ' + ' AND ' + 'xtype=' + 'U' + ')'

Print @SQLLine1A
select @SQLCreateExtractTable = 'CREATE TABLE ' + @ExtractTable + '(' + '[EXTRACT_KEY_ID]' + ',' + @columnnames + ')' + CHAR(13) + 'GO' + CHAR(13)

2

Would be curious to understand why you are doing this? What is the business case or usage for this?

3

I am writing tables for an extraction of data from one system to another.

4

I found something, and i only want to get the PRIMARY KEY Columns not the entire table.
Any help would be appreciated.

DECLARE
@object_name SYSNAME
, @object_id INT
, @SQL NVARCHAR(4000)

SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
, @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID('mytable', 'U')) o

SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(13) + ' , [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
ELSE
CASE WHEN c.system_type_id != c.user_type_id
THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
ELSE '[' + UPPER(tp.name) + ']'
END +
CASE
WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('nvarchar', 'nchar')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length / 2 AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
THEN ' COLLATE ' + c.collation_name
ELSE ''
END +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END +
CASE WHEN c.default_object_id != 0
THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
ELSE ''
END +
CASE WHEN cc.[object_id] IS NOT NULL
THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
ELSE ''
END +
CASE WHEN c.is_identity = 1
THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
ELSE ''
END
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id

inner join sys.indexes pk
    on c.object_id = pk.object_id 
    and pk.is_primary_key = 1


LEFT JOIN sys.check_constraints cc WITH(NOLOCK)   
     ON c.[object_id] = cc.parent_object_id   
    AND cc.parent_column_id = c.column_id  
WHERE c.[object_id] = @object_id  
ORDER BY c.column_id  
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +   
ISNULL((SELECT '  
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' +   
CASE WHEN i.index_id = 1   
    THEN 'CLUSTERED'   
    ELSE 'NONCLUSTERED'   
END +' (' + (  
SELECT STUFF(CAST((  
    SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +  
            CASE WHEN ic.is_descending_key = 1  
                THEN ' DESC'  
                ELSE ''  
            END  
    FROM sys.index_columns ic WITH(NOLOCK)  
    WHERE i.[object_id] = ic.[object_id]  
        AND i.index_id = ic.index_id  
    FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'  
FROM sys.indexes i WITH(NOLOCK)  
WHERE i.[object_id] = @object_id  
    AND i.is_primary_key = 1), '') + CHAR(13) + ');'

/*
inner join sys.indexes pk
on tab.object_id = pk.object_id
and pk.is_primary_key = 1
inner join sys.index_columns ic
on ic.object_id = pk.object_id
and ic.index_id = pk.index_id
inner join sys.columns col
on pk.object_id = col.object_id
and col.column_id = ic.column_id
where tab.[name] = 'EMPL'
order by schema_name(tab.schema_id),
pk.[name],
ic.index_column_id

*/

PRINT @SQL

5

hi

hope this helps :slight_smile:

please see below link ..
it will get all Primary Keys , Unique Constraints , Foreign Keys

You can filter
... if you want only for a table !! or only Foreign Keys etc etc etc

6

Thanks, i was able to complete it

7

hi

please don't mind my saying this !!!

why re-invent the wheel

There are TONS and TONS and TONS of
"READY" scripts written for all kinds of purposes
available on the INTERNET !!!

1 Like
8

Totally I found snippets and built it....