Jan 5, 2014

Write a sql query to get the script of create table statement in sql server


Write a sql query or script to get or generate the script or query or text of create table statement in sql server.

Step 1: Create sql function

ALTER FUNCTION GenerateTableScript(
    @TableName VARCHAR(500)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE @TableScript AS VARCHAR(MAX)
   
    SET @TableScript = 'CREATE TABLE [' + @TableName + '] ('
   
    SELECT @TableScript = @TableScript + '[' + c.name + '] ' +
         CASE c.is_computed
             WHENTHEN ' AS ' + (SELECT [definition] FROM sys.computed_columns WHERE object_id =  c.object_id)
             ELSE  ty.name +
            
                 CASE
                      WHEN ty.name = 'VARCHAR' OR  ty.name = 'NVARCHAR'  OR ty.name  = 'CHAR' THEN '(' + CASE c.max_length WHEN -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END  + ')'
                      WHEN ty.name  = 'DATETIME2' OR ty.name  = 'TIME' THEN  '(' + CAST(c.scale AS VARCHAR) + ')'
                      WHEN ty.name  = 'NUMERIC' OR ty.name  = 'DECIMAL' THEN '(' + CAST(c.precision AS VARCHAR)+ ',' + CAST(c.scale AS VARCHAR)+ ')'
                      ELSE ''
                 END +
            
                 CASE c.is_identity
                      WHEN 1 THEN (SELECT ' IDENTITY(' + CAST(ic.seed_value AS VARCHAR) + ',' + CAST(ic.increment_value AS VARCHAR) + ')' FROM sys.identity_columns ic WHERE object_id =  c.object_id )
                      ELSE ''
                 END +
            
                 CASE c.is_nullable
                      WHEN 1 THEN ' NULL'
                      ELSE ' NOT NULL'
                 END
         END +
        
         CASE
             WHEN c.column_id = t.max_column_id_used THEN ')'
             ELSE ', '
         END
    FROM sys.tables t
    INNER JOIN sys.columns c ON  t.object_id = c.object_id
    INNER JOIN sys.types  ty ON ty.user_type_id= c.user_type_id
    WHERE t.name = @TableName
    ORDER BY c.column_id
   
    RETURN @TableScript
END

Step 2: Execute the function and pass the table name as parameter: For example:

SELECT dbo. GenerateTableScript('tblStu')

Sample output:

CREATE TABLE [tblStu] (
    [one] bigint IDENTITY(5,3) NOT NULL,
    [two] int NOT NULL,
    [three] nvarchar(MAX) NULL,
    [four] uniqueidentifier NULL,
    [five] datetime2(7) NULL,
    [six] uniqueidentifier NULL,
    [seven] datetime NULL,
    [eight] time(7) NULL,
    [nine] decimal(18,3) NULL,
    [ten] numeric(18,4) NULL,
    [eleven] char(10) NULL,
    [twelve] geography NULL,
    [thirteen]  AS ([two]+(10))
)

No comments: