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))
)

1 comment:

  1. The reason for testing is to give the schools a tool to measure different students from different backgrounds on the same categories of learning on an equal footing.term paper The same rationale carries through from private school testing to admissions tests used when entering colleges and universities and then graduate schools.

    ReplyDelete