Generate **bare** CREATE TABLE and CREATE PROC statemnts in SQL Server

Question

The deployment procedures in my company require that SQL scripts for creation of procedures and tables, etc. be bare.

Generating create scripts in SQL Server Management Studio gives you significantly more than a bare script, which is unfortunate in this case. For example, for a test table this is what SSMS generated for me:

USE [DemoDB]
GO

/****** Object:  Table [dbo].[example]    Script Date: 08/07/2012 15:46:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[example](
    [fake_row] [int] NULL
) ON [PRIMARY]

GO

So, what I'd like to know is:

Can I somehow run a query on SQL Server in SSMS that would give me a script that was literally just the following?

create table example (
    fake_row int
)

Answer 1

You can create your own "create table" statements, using information_schema (Aaron . . . I know, INFORMATION_SCHEMA ;).

I have written some code for this purpose, but it doesn't use INFORMATION_SCHEMA. I know, if uses older system tables that are probably scheduled to be removed. It also adds in some additional information (which you can remove, since you don't need it). Just put the list of tables in the @INCLUSIONLIST and run this in the database where the tables reside.

SET @INCLUSIONLIST = '|table1|table2|';

SELECT (CASE WHEN rownum = 1 THEN 'CREATE TABLE ['+a.t_name+'] ('
             WHEN c.column_id IS NOT NULL
             THEN '    ['+c.c_name+'] ' +
                  (CASE WHEN CHARINDEX('CHAR', datatype) > 0 THEN datatype+'('+(case when length < 0 then 'max' else cast(length as varchar) end)+')'
                        WHEN CHARINDEX('BINARY', datatype) > 0 THEN datatype+'('+cast(length as varchar)+')'
                        WHEN datatype = 'float' AND precision <> 24 THEN datatype+'('+cast(precision as varchar)+')'
                        WHEN datatype IN ('numeric', 'decimal') AND scale = 0 THEN datatype+'('+cast(precision as varchar)+')'
                        WHEN datatype IN ('numeric', 'decimal') AND scale > 0 THEN datatype+'('+cast(precision as varchar)+','+cast(scale as varchar)+')'
                        ELSE datatype END)+' '+
                  (CASE WHEN c.identity_seed IS NOT NULL
                        THEN 'IDENTITY(' + CAST(identity_seed AS VARCHAR) + ',' + CAST(identity_increment AS VARCHAR) + ') '
                        ELSE '' END) +
                  (CASE WHEN c.is_nullable = 0 THEN 'NOT NULL ' ELSE '' END) +
                  (CASE WHEN c.default_definition IS NOT NULL
                        THEN 'DEFAULT '+default_definition ELSE '' END) +
                  (CASE WHEN max_column_id = column_id AND pk.pk_name IS NULL THEN '' ELSE ',' END)
            WHEN rownum = max_column_id + 2 and pk.pk_name IS NOT NULL
            THEN '    PRIMARY KEY ('+pk.pk_columns+')'
            WHEN rownum = max_column_id + 3 THEN ')  /* CREATE TABLE '+a.t_name+' */'
            WHEN rownum = max_column_id + 4 THEN 'GO'
            WHEN rownum = max_column_id + 5 THEN ''
        END)
 FROM (SELECT t.t_name, rownum, max_column_id
       FROM (SELECT t.name as t_name, MAX(c.column_id) as max_column_id
             FROM sys.columns c join
                  (SELECT * FROM sys.tables WHERE CHARINDEX('|'+name+'|', @INCLUSIONLIST) > 0 ) t 
                  ON c.object_id = t.object_id
             GROUP BY t.name) t join
            (SELECT ROW_NUMBER() OVER (ORDER BY object_id) as rownum FROM sys.columns c) ctr
            ON ctr.rownum <= t.max_column_id + 5
      ) a LEFT OUTER JOIN
      (SELECT t.name as t_name, c.column_id, c.name AS c_name, u.name as datatype,
              ISNULL(baset.name, N'') AS systemtype,
              CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND c.max_length <> -1
                        THEN c.max_length/2 ELSE c.max_length END AS INT) AS length,
              c.precision AS precision,
              c.scale as scale,
              c.is_nullable,
              dc.definition as default_definition,
              idc.seed_value as identity_seed, idc.increment_value as identity_increment
       FROM sys.tables t JOIN
            sys.all_columns AS c
            ON c.object_id = t.object_id LEFT OUTER JOIN
            sys.types u
            ON u.user_type_id = c.user_type_id LEFT OUTER JOIN
            sys.types baset
            ON baset.user_type_id = c.system_type_id AND 
               baset.user_type_id = baset.system_type_id LEFT OUTER JOIN
            sys.default_constraints dc
            ON c.object_id = dc.parent_object_id AND
               c.column_id = dc.parent_column_id LEFT OUTER JOIN
            sys.identity_columns idc
            ON c.object_id = idc.object_id AND
               c.column_id = idc.column_id
      ) c
      ON a.t_name = c.t_name AND
         c.column_id + 1 = a.rownum LEFT OUTER JOIN
      (SELECT t.name as t_name, kc.name as pk_name, 
              (MAX(CASE WHEN index_column_id = 1 THEN '['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 2 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 3 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 4 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 5 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 6 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 7 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 8 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 9 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 10 THEN ','+'['+c.name+']' ELSE '' END)
              ) as pk_columns
       FROM sys.indexes i JOIN
            sys.key_constraints kc
            ON i.name = kc.name AND
               kc.type = 'PK' JOIN
            sys.tables t
            ON i.object_id = t.object_id JOIN
            sys.index_columns ic
            ON i.object_id = ic.object_id AND
           i.index_id = ic.index_id JOIN
            sys.columns c
            ON ic.index_column_id = c.column_id AND
               ic.object_id = c.object_id
       GROUP BY t.name, kc.name
      ) pk
      ON pk.t_name = a.t_name 
ORDER BY a.t_name, rownum