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
)
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
Apologies for the self-answer, marking other good answers +1.
It turns out you can get pretty much all of the way there with utility in SSMS.
- Right click on the database in the object explorer
- Click "Tasks"
- Click "Generate Scripts"
- Select "Script Specific Objects" and choose tables, or whatever else
- Select "Save To File" & "Single File Per Object" (or just spool to query window)
- Click "Advanced" and you can enable/disable the generation of virtually every part of the statements you're generating including constraints, use statements, etc.
- If further removal is required (e.g. removing GO), it's minimal
Optionally, you can control the general scripting behavior of SSMS like so: Options for scripting SQL Server database objects.
I would create stored procs that read from the INFORMATION_SCHEMA views.
Getting the stored proc text is just:
DECLARE @SQL VARCHAR(8000)
SET @SQL=' '
select @SQL = @SQL + ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
AND SPECIFIC_NAME = 'updateComments'
PRINT @SQL
The one to script the table would looks something like:
DECLARE @tableName VARCHAR(50)
SET @tableName = 'Location'
-- Need to know how many columns
DECLARE @NumColumns int
SELECT @NumColumns = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
DECLARE @SQL VARCHAR(8000)
SET @SQL=' '
DECLARE @Results TABLE
(LineNumber int,
Line VARCHAR(1000))
INSERT INTO @Results
SELECT 0 AS ORDINAL_POSITION,
'CREATE TABLE '+TABLE_SCHEMA+'.'+@tableName+' (' AS Line
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tableName
UNION ALL
SELECT ORDINAL_POSITION,
'['+COLUMN_NAME+'] '+ DATA_TYPE +
CASE WHEN DATA_TYPE = 'varchar' OR DATA_TYPE = 'char'
THEN '('+ CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) +')'
ELSE '' END +
CASE WHEN IS_NULLABLE = 'NO'
THEN ' NULL '
ELSE ' NOT NULL' END +
CASE WHEN ORDINAL_POSITION < @NumColumns
THEN ', '
ELSE ' ' END
AS LINE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
UNION ALL
SELECT 999, ' )'
SELECT Line
FROM @Results
ORDER BY LineNumber
SELECT @SQL = @SQL + Line
FROM @Results
ORDER BY LineNumber
SELECT @SQL
PRINT @SQL
This creates the simple table script that you want, but it is not complete - doesn't do numeric precision, primary key, etc. But this is enough to get you started.
I would just make each one of these into a stored proc that takes the object name as a parameter.
