Extracting the DDL for a table
-
ok, this may make your head spin, because of the size of the SQL.
I've already written some procs/functions that extract the DDL of any table by simply passing in the table name; it includes constriants,foreign keys, check constraints, everything, in a format that i personally like.
There's several ways to enhance what i already did...i was returning a single 8000 varchar string, but that was limited to small table definitions; also since i was accessing so many sys tables, i thought i might revisit that. I figured I'd rewrite it to only use INFORMATION_SCHEMA views, but some data is simply not in the views.
Anyway, i'm getting some duplicate rows, and i can't seem to see the reason why. could someone paste this script, change the SET @TABLENAME='GMCDBGACCOMP' command to something in your database, and tell me what i'm missing as far as the duplicate row issue related to check constraints (i think)
also I haven't finished translating the foreign keys, but after i get this duplicate row issue resolved, i'll post the whole thing here and as a script contribution.
I think this script would be really handy for a lot of folks when finished.
SET NOCOUNT ON
DECLARE @TABLENAME VARCHAR(50),
@STRINGLEN INT,
@MAXCOL INT,
@VBCRLF VARCHAR(2)
DECLARE @SQLRESULTS TABLE(TBLID INT IDENTITY(1,1) PRIMARY KEY,SQLSTATEMENTS VARCHAR(8000) )
DECLARE @CONSTRAINT_CLAUSE VARCHAR(4000)
SET @TABLENAME='GMCDBGACCOMP'
SET @VBCRLF=CHAR(13) + CHAR(10)
SELECT @STRINGLEN = MAX(LEN(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLENAME
SELECT @MAXCOL = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLENAME
INSERT INTO @SQLRESULTS(SQLSTATEMENTS)
SELECT DISTINCT 'CREATE TABLE [' + UPPER([TABLE_NAME]) + '] ( ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLENAME
INSERT INTO @SQLRESULTS(SQLSTATEMENTS)
SELECT @VBCRLF + '[' + UPPER([A].[COLUMN_NAME]) + '] ' + SPACE(@STRINGLEN - LEN([A].[COLUMN_NAME])) + UPPER(A.DATA_TYPE)
+ CASE
--ie numeric(10,2)
WHEN A.DATA_TYPE IN ('decimal','numeric') THEN
' (' + CONVERT(VARCHAR,A.NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR,A.NUMERIC_SCALE) + ')' + SPACE(6 - LEN(CONVERT(VARCHAR,A.NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR,A.NUMERIC_SCALE))) + SPACE(7)
+ SPACE(12 - LEN(A.DATA_TYPE)) + CASE WHEN A.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL'
END
--ie float(53)
WHEN A.DATA_TYPE IN ('float') THEN
' ('+ CONVERT(VARCHAR,A.NUMERIC_PRECISION) + ')' + SPACE(6 - LEN(CONVERT(VARCHAR,A.NUMERIC_PRECISION))) + SPACE(7)
+ SPACE(12 - LEN(A.DATA_TYPE)) + CASE WHEN A.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL'
END
--ie VARCHAR(40)
WHEN A.DATA_TYPE IN ('char','VARCHAR','nchar','nVARCHAR') THEN
' ('+ CONVERT(VARCHAR,A.CHARACTER_MAXIMUM_LENGTH) + ')' + SPACE(6 - LEN(CONVERT(VARCHAR,A.CHARACTER_MAXIMUM_LENGTH))) + SPACE(7)
+ SPACE(12 - LEN(A.DATA_TYPE)) + CASE WHEN A.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL'
END
WHEN A.DATA_TYPE IN ('datetime','money','text','image') THEN
+ SPACE(14 - LEN(A.DATA_TYPE)) + ' ' + CASE WHEN A.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL'
END
--ie int
ELSE
+ SPACE(12 - LEN(DATA_TYPE)) + CASE WHEN E.AUTOVAL IS NULL THEN ' ' ELSE ' IDENTITY(' + CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TABLENAME),1) ) + ',' + CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TABLENAME),1) ) + ')' END
+ SPACE(2) + CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL'
END
END
+ CASE WHEN A.COLUMN_DEFAULT IS NULL THEN ' ' ELSE ' DEFAULT ' + A.COLUMN_DEFAULT END
+ CASE WHEN C.CONSTRAINT_NAME IS NULL THEN ' ' ELSE ' CHECK ' + C.CHECK_CLAUSE END
+ CASE WHEN A.ORDINAL_POSITION = @MAXCOL THEN '' ELSE ',' END
--'primary key
--'foreign key
--closing parenthesis
FROM INFORMATION_SCHEMA.COLUMNS A
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B ON A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS C ON B.CONSTRAINT_NAME=C.CONSTRAINT_NAME
LEFT OUTER JOIN SYSOBJECTS D ON A.TABLE_NAME= D.[NAME] AND .NAME=@TABLENAME">D.NAME=@TABLENAME
INNER JOIN SYSCOLUMNS E ON D.ID=E.ID AND E.[NAME]=A.COLUMN_NAME
WHERE A.TABLE_NAME=@TABLENAME
AND C.CONSTRAINT_NAME IS NULL OR C.CONSTRAINT_NAME IN(SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE ='CHECK' )
ORDER BY ORDINAL_POSITION
--CONSTRAINT [PK__AGENCY_WEB_USER___3BB699D9] PRIMARY KEY ([USER_AGREEMENT_ID]
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=@TABLENAME AND CONSTRAINT_TYPE = 'PRIMARY KEY')
UPDATE @SQLRESULTS SET SQLSTATEMENTS = SQLSTATEMENTS + ',' WHERE TBLID=(SELECT MAX(TBLID) FROM @SQLRESULTS)
SET @CONSTRAINT_CLAUSE=''
SELECT @CONSTRAINT_CLAUSE=@CONSTRAINT_CLAUSE + '[' + B.COLUMN_NAME + '],' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
WHERE A.TABLE_NAME = @TABLENAME AND A.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY A.CONSTRAINT_NAME
INSERT INTO @SQLRESULTS(SQLSTATEMENTS)
SELECT ' CONSTRAINT [' + A.CONSTRAINT_NAME + '] ' + CONSTRAINT_TYPE + ' (' + substring(@CONSTRAINT_CLAUSE,1,len(@CONSTRAINT_CLAUSE) -1) + ')'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
WHERE A.TABLE_NAME=@TABLENAME AND A.CONSTRAINT_TYPE = 'PRIMARY KEY'
--unique constraints more than one to a table
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=@TABLENAME AND CONSTRAINT_TYPE = 'UNIQUE')
UPDATE @SQLRESULTS SET SQLSTATEMENTS = SQLSTATEMENTS + ',' WHERE TBLID=(SELECT MAX(TBLID) FROM @SQLRESULTS)
SET @CONSTRAINT_CLAUSE=''
SELECT @CONSTRAINT_CLAUSE=@CONSTRAINT_CLAUSE + '[' + B.COLUMN_NAME + '],' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
WHERE A.TABLE_NAME = @TABLENAME AND A.CONSTRAINT_TYPE = 'UNIQUE' ORDER BY A.CONSTRAINT_NAME
INSERT INTO @SQLRESULTS(SQLSTATEMENTS)
SELECT ' CONSTRAINT [' + A.CONSTRAINT_NAME + '] ' + CONSTRAINT_TYPE + ' (' + substring(@CONSTRAINT_CLAUSE,1,len(@CONSTRAINT_CLAUSE) -1) + ')'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
WHERE A.TABLE_NAME=@TABLENAME AND A.CONSTRAINT_TYPE = 'UNIQUE'
--close the table definition
INSERT INTO @SQLRESULTS(SQLSTATEMENTS)
SELECT ' )'
SELECT SQLSTATEMENTS FROM @SQLRESULTS ORDER BY TBLID
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi all,
My post doesn't answer Lowell's problem (sorry Lowell!
), but here's a related article I wrote a few years ago...http://www.sqlservercentral.com/columnists/rrandall/creatingascriptfromastoredprocedure.asp
The title was originally "Creating a 'create table' script from a stored procedure", but it got edited somewhere along the way
Here's the usage (note the 8000 limit though - as Lowell mentioned too)...
declare @object_text varchar(8000)
exec get_create_table_script @object_text output, 'my_server',
'my_database', 'my_table'
select @object_text
This will give you the same DDL for a table as is created when you do 'Generate SQL Script...' in Enterprise Manager.
It might be useful for some of you out there
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
LOL......I overcome the 8000 char limit by using temp tables to hold the scripts...and do the full database.....want mine too?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GenerateBuildScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GenerateBuildScript]
GO
CREATE Proc usp_GenerateBuildScript
@DB_TO_CREATE char(2)
As
Set Nocount On
Declare @Object_type Varchar(512),
@Object_name Varchar(512),
@table_name varchar(512) ,
@Column_id smallint,
@MaxID int,
@column_name varchar(512),
@constraint_name varchar(512),
@data_type varchar(64),
@length smallint,
@prec smallint,
@scale int,
@is_nullable char(1),
@is_identity char(1),
@Filegroup varchar(50),
@TypeString Varchar(100),
@Child_Table varchar(128),
@Child_column varchar(128),
@Parent_Table varchar(128),
@Parent_column varchar(128),
@SQLTxt Varchar(6000),
@Pos int,
@Header Varchar(1000),
@List1 Varchar(1000),
@Mid Varchar(1000),
@List2 Varchar(1000),
@Trailer Varchar(1000),
@dbname sysname ,
@Olddbname sysname ,
@exec_stmt nvarchar(625),
@showdev bit,
@allstatopts int,
@allrelstatopts int,
@allcatopts int,
@name sysname,
@curdbid int,
@cmd varchar(8000),
@bitdesc varchar(35) /* db option English description */
set nocount on
Set @dbname = 'OrderFullfillment'
Set @Olddbname = 'MVDB'
CREATE TABLE #BuildLines (
[Object_Type] [varchar] (30) NULL ,
[table_name] [varchar] (512) NULL ,
[column_name] [varchar] (512) NULL ,
[column_id] [smallint] NULL ,
[data_type] [varchar] (64) NULL ,
[length] [smallint] NULL ,
[prec] [smallint] NULL ,
[scale] [int] NULL ,
[default_value] [varchar] (2000) NULL,
[default_cons_name] [varchar] (128) NULL ,
[is_nullable] [varchar] (1) NULL ,
[is_identity] [varchar] (1) NULL ,
[constraint_name] [varchar] (512) NULL ,
[condition] [varchar] (3000) ,
[pos] [smallint] NULL,
[child_table] [varchar] (128) NULL ,
[child_column] [varchar] (128) NULL ,
[parent_table] [varchar] (128) NULL ,
[parent_column] [varchar] (128) NULL ,
[index_name] [varchar] (128) NULL ,
[is_unique] [varchar](1) NULL,
[Filegroup] [varchar] (50) NULL
)
Create Table #Script ([ID] bigint IDENTITY (1, 1) NOT NULL,
[Object_Type] [varchar] (30) NULL ,
[Object_name] [varchar] (512) NULL ,
[pos] [smallint] NULL,
[Text] [Varchar] (7000)
)
create table #spdboption
(
dboption varchar(255) null
)
create table #spFileList
(name sysname,
fileid int,
filename nchar(260),
filegroup sysname null,
size nvarchar(15),
maxsize nvarchar(15),
growth nvarchar(15),
usage nvarchar(10),
status int)
/*
** See if the database exists
*/
select @curdbid=dbid from master.dbo.sysdatabases
where (name = @Olddbname)
if @curdbid is null
begin
raiserror(15010,-1,-1,@Olddbname)
end
/*
** Get bitmap of all options that can be set by sp_dboption.
*/
select @allstatopts=number from master.dbo.spt_values
where type = 'D' and name = 'ALL SETTABLE OPTIONS'
select @allrelstatopts=number from master.dbo.spt_values
where type = 'D2' and name = 'ALL SETTABLE OPTIONS'
select @allcatopts=number from master.dbo.spt_values
where type = 'DC' and name = 'ALL SETTABLE OPTIONS'
/*
** Check if you have access to database
*/
if (has_dbaccess(@Olddbname) <> 1)
begin
raiserror(15622,-1,-1, @Olddbname)
return
end
/*
** build the database status
** description.
*/
/*
** First check bits in sysdatabases.status.
*/
/*
** Check select into/bulk copy bit (4)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 4
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check truncate log on checkpoint bit (8)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 8
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check no checkpoint on recovery bit (16)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 16
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check don't recover bit (32)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 32
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check not recovered only bit (256)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 256
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check offline bit (512)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 512
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check read only bit (1024)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 1024
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check dbo only bit (2048)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 2048
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check single user bit (4096)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D'
and d.status & v.number = 4096
and v.number <> @allstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check ANSI NULL default bit (16384)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D2'
and d.status & v.number = 16384
and v.number <> @allrelstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check ANSI empty string bit (65536)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D2'
and d.status & v.number = 65536
and v.number <> @allrelstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check recursive triggers bit (131072)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'D2'
and d.status & v.number = 131072
and v.number <> @allrelstatopts /* all status options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Now check bits in sysdatabases.category.
*/
/*
** Check published bit (1)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'DC'
and d.category & v.number = 1
and v.number <> @allcatopts /* all options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check subscribed bit (2)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'DC'
and d.category & v.number = 2
and v.number <> @allcatopts /* all options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check merge publish bit (4)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'DC'
and d.category & v.number = 4
and v.number <> @allcatopts /* all options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/*
** Check distributed bit (16)
*/
select @bitdesc = null
select @bitdesc = v.name
from master.dbo.spt_values v, master.dbo.sysdatabases d
where d.dbid = @curdbid
and v.type = 'DC'
and d.category & v.number = 16
and v.number <> @allcatopts /* all options */
if @bitdesc IS NOT NULL
begin
insert #spdboption (dboption) values (@bitdesc)
end
/* store the list of files */
/* need to get the list of files for the specific database, so must exec the insert */
select @exec_stmt = 'insert into #spfiledesc
select db_id (N'+ quotename(@name, '''') + '), fileid, status, size, name from '
+ quotename(@name, '[') + '.dbo.sysfiles'
execute (@exec_stmt)
select @cmd = 'Insert #spFileList(name, fileid, filename, filegroup, size, maxsize, growth, usage, status)'
+ 'select name, fileid, filename, groupname, convert(nvarchar(15), size * 8) + N'' KB'', '
+ '(case maxsize when -1 then N''Unlimited'''
+ 'else '
+ 'convert(nvarchar(15), maxsize * 8) + N'' KB'' end), '
+ '(case ' + quotename(@Olddbname, '[') + '.dbo.sysfiles.status & 0x100000 when 0x100000 then '
+ 'convert(nvarchar(3), growth) + N''%'' '
+ 'else '
+ 'convert(nvarchar(15), growth * 8) + N'' KB'' end), '
+ '(case ' + quotename(@Olddbname, '[') + '.dbo.sysfiles.status & 0x40 when 0x40 then ''log only'' else ''data only'' end), '
+ quotename(@Olddbname, '[') + '.dbo.sysfilegroups.status '
+ 'from ' + quotename(@Olddbname, '[') + '.dbo.sysfiles'
+ ' left outer join ' + quotename(@Olddbname, '[') + '.dbo.sysfilegroups'
+ ' on ' + quotename(@Olddbname, '[') +'.dbo.sysfiles.groupid = '
+ quotename(@Olddbname, '[') + '.dbo.sysfilegroups.groupid'
exec (@cmd)
/*select substring(@cmd, 1, 255)
select substring(@cmd, 256, 255)
select substring(@cmd, 512, 255)
select * from #spFileList */
/* create a temporary table to hold the code. This allows us to remove (painfully) extraneous
commas */
create table #TheCode
(TheKey int Identity,
TheLine varchar(8000))
create unique index #TheCode_IDX on #TheCode (TheKey)
/* now begin building the Create Statement */
Insert #TheCode(TheLine)
SELECT 'Use master'
Insert #TheCode(TheLine)
SELECT 'CREATE DATABASE [' + @dbname + ']'
insert #TheCode(TheLine)
SELECT 'ON PRIMARY '
/* generate the filespecs for the primary file group */
insert #TheCode(TheLine)
select '(NAME = ' + rtrim(name) + ', FILENAME = ''' + rtrim(filename)
+ ''', SIZE = ' + size + ', MAXSIZE = ' + maxsize
+ ' , FILEGROWTH = ' + growth + '), '
from #spFileList
where upper(FileGroup) = N'PRIMARY'
/* build a list of secondary file group names */
declare SecondaryFG cursor for select DISTINCT filegroup from #spFileList
where FileGroup <> N'Primary'
Open SecondaryFG
fetch SecondaryFG into @filegroup
while @@FETCH_STATUS <> -1
begin
insert #TheCode(TheLine)
SELECT 'FILEGROUP [' + rtrim(@filegroup) + ']'
insert #TheCode(TheLine)
select '(NAME = ' + rtrim(name) + ', FILENAME = ''' + rtrim(filename)
+ ''', SIZE = ' + size + ', MAXSIZE = ' + maxsize
+ ' , FILEGROWTH = ' + growth + '), '
from #spFileList
where upper(FileGroup) = @filegroup
and usage = N'data only'
fetch SecondaryFG into @filegroup
end
close SecondaryFG
Deallocate SecondaryFG
/* and now deal with the log files */
insert #TheCode(TheLine)
SELECT 'LOG ON '
insert #TheCode(TheLine)
select '(NAME = ' + rtrim(name) + ', FILENAME = ''' + rtrim(filename)
+ ''', SIZE = ' + size + ', MAXSIZE = ' + maxsize
+ ' , FILEGROWTH = ' + growth + '),'
from #spFileList
where usage = N'log only'
/* now deal with trailing commas */
declare CommaProc cursor scroll for select TheLine from #TheCode FOR UPDATE
declare @TheLine varchar(8000)
Open CommaProc
Fetch CommaProc into @TheLine
If @@Fetch_Status <> -1
begin
/* find the LOG ON line so that we can remove the comma from its predecessor */
While @TheLine not like 'LOG %'
begin
fetch Next from CommaProc into @TheLine
end
/* now we should be sitting on the LOG ON line; get the prev line */
Fetch Prior from CommaProc into @TheLine
select @TheLine = substring(@TheLine, 1, LEN(@TheLine) - 1)
update #TheCode
set TheLine = @TheLine where Current of CommaProc
/* now there's one more comma to deal with on the last line of the Log files */
Fetch last from CommaProc into @TheLine
select @TheLine = substring(@TheLine, 1, LEN(@TheLine) - 1)
update #TheCode
set TheLine = @TheLine where Current of CommaProc
end
close CommaProc
deallocate CommaProc
Insert #TheCode(TheLine)
SELECT ' '
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Database','Create','1',TheLine from #TheCode
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Database','Create','2',''
/* Now generate file group properties */
/* output default file group, if any */
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select distinct 'Database','Alter','3','ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP ' + filegroup + ' DEFAULT'
from #spFileList
where status = 16 and filegroup <> N'PRIMARY'
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Database','Alter','4',''
/* output read-only filegroups, if any */
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Database','Alter','5','ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP ' + filegroup + ' READONLY'
from #spFileList
where status = 8
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Database','Alter','6',''
/* now generate db_option settings */
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Database','Alter','7','exec sp_dboption [' + @dbname + '], ''' + dboption + ''', true' + CHAR(13)+char(10) + ''
from #spdboption
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Database','Alter','8','Go'
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Database','Alter','8','Use ' + @dbname
/* Get columns */
INSERT INTO #BuildLines([Object_Type],
[table_name],
[column_name],
[column_id],
[data_type],
[length],
[prec],
[scale],
[default_value],
[default_cons_name],
[is_nullable],
[is_identity])
SELECT 'Table',
tab.name,
col.name,
col.colid,
typ.name,
col.length,
col.prec,
col.scale,
com.text,
obj.name,
CASE
WHEN col.isnullable = 1 THEN 'Y'
ELSE 'N'
END ,
CASE
WHEN col.status & 0x80 = 0x80 THEN 'Y'
ELSE 'N'
END isnullable
FROM sysobjects tab,
syscolumns col LEFT OUTER JOIN
syscomments com INNER JOIN
sysobjects obj ON com.id = obj.id ON
col.cdefault = com.id AND com.colid = 1,
systypes typ
WHERE tab.id = col.id
AND tab.xtype = 'U'
AND tab.name In (Select 'Table' From DBADB.dbo.Object_Matrix Where OrderFullfillment = 'Y')
AND col.xusertype = typ.xusertype
ORDER BY 1, 3
/* Get check constraints */
INSERT INTO #BuildLines([Object_Type],
[table_name],
[constraint_name],
[condition])
SELECT 'ChkCon',
Convert(varchar(512),tab.name) ,
Convert(Varchar(512),obj.name),
Convert(Varchar(3000),com.text)
FROM sysobjects tab,
syscomments com,
sysobjects obj
WHERE obj.xtype = 'C'
AND com.id = obj.id
AND tab.id = obj.parent_obj
AND tab.name In (Select 'Table' From DBADB.dbo.Object_Matrix Where OrderFullfillment = 'Y')
ORDER BY 1, 2
/* Get primary key constraints */
INSERT INTO #BuildLines([Object_Type],
[table_name],
[constraint_name],
[column_name],
[pos])
SELECT 'PriKey',
tab.name
table_name,
ind.name
constraint_name,
INDEX_COL(tab.name, ind.indid, idk.keyno)
column_name,
idk.keyno pos
FROM sysobjects tab,
sysindexes ind,
sysindexkeys idk
WHERE ind.status & 0x800 = 0x800
AND ind.id = tab.id
AND idk.id = tab.id
AND idk.indid = ind.indid
AND tab.name In (Select 'Table' From DBADB.dbo.Object_Matrix Where OrderFullfillment = 'Y')
ORDER BY 1, 2, 4
/* Get unique key constraints */
INSERT INTO #BuildLines([Object_Type],
[table_name],
[constraint_name],
[column_name],
[pos])
SELECT 'UniqueKey',
tab.name
table_name,
ind.name
constraint_name,
INDEX_COL(tab.name, ind.indid, idk.keyno)
column_name,
idk.keyno pos
FROM sysobjects tab,
sysindexes ind,
sysindexkeys idk
WHERE ind.status & 0x1000 = 0x1000
AND ind.id = tab.id
AND idk.id = tab.id
AND idk.indid = ind.indid
AND tab.name In (Select 'Table' From DBADB.dbo.Object_Matrix Where OrderFullfillment = 'Y')
ORDER BY 1, 2, 4
/* Get foreign key constraints */
INSERT INTO #BuildLines([Object_Type],
[child_table],
[constraint_name],
[child_column],
[pos],
[parent_table],
[parent_column])
SELECT 'ForKey',
child_table child_table,
obj.name constraint_name,
child_column child_column,
child_pos pos,
parent_table parent_table,
parent_column parent_column
FROM (
SELECT tab1.name child_table,
col1.name child_column,
CASE col1.colid
WHEN ref.fkey1 THEN 1
WHEN ref.fkey2 THEN 2
WHEN ref.fkey3 THEN 3
WHEN ref.fkey4 THEN 4
WHEN ref.fkey5 THEN 5
WHEN ref.fkey6 THEN 6
WHEN ref.fkey7 THEN 7
WHEN ref.fkey8 THEN 8
WHEN ref.fkey9 THEN 9
WHEN ref.fkey10 THEN 10
WHEN ref.fkey11 THEN 11
WHEN ref.fkey12 THEN 12
WHEN ref.fkey13 THEN 13
WHEN ref.fkey14 THEN 14
WHEN ref.fkey15 THEN 15
WHEN ref.fkey16 THEN 16
END child_pos,
tab2.name parent_table,
col2.name parent_column,
ref.constid constraint_id,
CASE col2.colid
WHEN ref.rkey1 THEN 1
WHEN ref.rkey2 THEN 2
WHEN ref.rkey3 THEN 3
WHEN ref.rkey4 THEN 4
WHEN ref.rkey5 THEN 5
WHEN ref.rkey6 THEN 6
WHEN ref.rkey7 THEN 7
WHEN ref.rkey8 THEN 8
WHEN ref.rkey9 THEN 9
WHEN ref.rkey10 THEN 10
WHEN ref.rkey11 THEN 11
WHEN ref.rkey12 THEN 12
WHEN ref.rkey13 THEN 13
WHEN ref.rkey14 THEN 14
WHEN ref.rkey15 THEN 15
WHEN ref.rkey16 THEN 16
END parent_pos
FROM syscolumns col1,
sysobjects tab1,
syscolumns col2,
sysobjects tab2,
sysreferences ref
WHERE col1.id = ref.fkeyid
AND tab2.name In (Select 'Table' From DBADB.dbo.Object_Matrix Where OrderFullfillment = 'Y')
AND tab1.id = col1.id
AND col2.id = ref.rkeyid
AND tab2.id = col2.id
AND col1.colid IN (ref.fkey1, ref.fkey2,
ref.fkey3, ref.fkey4, ref.fkey5, ref.fkey6, ref.fkey7,
ref.fkey8, ref.fkey9, ref.fkey10, ref.fkey11,
ref.fkey12, ref.fkey13, ref.fkey14, ref.fkey15,
ref.fkey16)
AND col2.colid IN (ref.rkey1, ref.rkey2,
ref.rkey3, ref.rkey4, ref.rkey5, ref.rkey6, ref.rkey7,
ref.rkey8, ref.rkey9, ref.rkey10, ref.rkey11,
ref.rkey12, ref.rkey13, ref.rkey14, ref.rkey15,
ref.rkey16))
foreignkeycols,
sysobjects obj
WHERE child_pos = parent_pos
AND obj.id = constraint_id
ORDER BY 1, 2, 4
/* Get indexes except primary keys and keys enforcing
unique constraints */
INSERT INTO #BuildLines([Object_Type],
[table_name],
[index_name],
[column_name],
[is_unique])
SELECT 'Index',
tab.name
table_name,
ind.name
index_name,
INDEX_COL(tab.name, ind.indid, idk.keyno)
column_name,
CASE
WHEN ind.status & 0x2 = 0x2 THEN 'Y'
ELSE 'N'
END is_unique
FROM sysindexes ind,
sysindexkeys idk,
sysobjects tab
WHERE NOT (ind.status & 0x800 = 0x800)
AND NOT (ind.status & 0x1000 = 0x1000)
AND idk.id = tab.id
AND idk.indid = ind.indid
AND tab.xtype = 'U'
AND tab.id = ind.id
AND ind.name not like '_WA%'
AND tab.name In (Select 'Table' From DBADB.dbo.Object_Matrix Where OrderFullfillment = 'Y')
ORDER BY 1, 2
--Select * From #BuildLines
--All objects accounted for, now lets drop existing, in case it's there.
/*
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
Select distinct 'Drop Index', index_name , '1', 'IF EXISTS (SELECT name FROM sysIndexes WHERE name = ' + index_name + ') DROP Index ' + table_name + '.' + index_name + '
'
from #BuildLines
Where Object_Type = 'Index'
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
Select distinct 'Drop Table', table_name , '1', 'IF EXISTS (SELECT name FROM sysobjects WHERE name = ' + table_name + ') DROP Table ' + table_name + '
'
from #BuildLines
Where Object_Type = 'Table'
*/
--Set up filegroup information, just in case
Update #BuildLines
Set Filegroup = A.Filegroup
From (SELECT sysobjects.name AS table_name, sysfilegroups.groupname AS filegroup
FROM sysobjects INNER JOIN
sysindexes ON sysobjects.id = sysindexes.id INNER JOIN
sysfilegroups ON sysindexes.groupid = sysfilegroups.groupid
WHERE (sysindexes.indid = 0 OR
sysindexes.indid = 1) AND (sysobjects.xtype = 'U')) as A
Where A.table_name = #BuildLines.table_name
--Set up index filegroup information, just in case as well
Update #BuildLines
Set Filegroup = A.Filegroup
From (SELECT sysindexes.name AS index_name, sysfilegroups.groupname AS filegroup
FROM sysindexes INNER JOIN
sysfilegroups ON sysindexes.groupid = sysfilegroups.groupid
WHERE (sysindexes.indid = 0 OR
sysindexes.indid = 1) ) as A
Where A.index_name = #BuildLines.index_name
--the create table statements
declare TableList cursor scroll for
select distinct table_name , Column_id, column_name,data_type,length,prec,scale,is_nullable,is_identity,[Filegroup]
from #BuildLines
Where Object_Type = 'Table'
open TableList
fetch first
from TableList
into @table_name , @Column_id, @column_name,@data_type,@length,@prec,@scale,@is_nullable,@is_identity,@Filegroup
while @@fetch_status <> -1
begin
--do the column adds here....
Set @TypeString = ''
If (@data_type = 'Int' OR @data_type = 'Bit' OR @data_type = 'float' OR @data_type = 'Datetime' OR @data_type = 'smallint' OR @data_type = 'money' OR @data_type = 'Real' OR @data_type = 'tinyint' OR @data_type = 'Text' OR @data_type = 'smalldatetime' OR @data_type = 'numeric')
Begin
Set @TypeString = '] [' + @data_type + '] '
End
If (@data_type = 'Char' OR @data_type = 'Varchar' OR @data_type = 'nvarchar')
Begin
Set @TypeString = '] [' + @data_type + '] (' + Convert(varchar(10),@length) + ')'
End
If (@data_type = 'Decimal' )
Begin
Set @TypeString = '] [' + @data_type + '] (' + Convert(varchar(10),@length) + ',' + Convert(varchar(10),@scale) +')'
End
If @TypeString = '' or @TypeString is null
Begin
Select 'Fix ' + @data_type
End
Select @MaxId = Max(Column_id) from #BuildLines
Where table_name = @table_name
Group By table_name
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
Select 'Create Table', @table_name , @Column_id,
Case
When (@column_id = 1 And @is_nullable = 'Y' and @is_identity = 'Y') then 'Create Table ' + @table_name + '( [' + @column_name + @TypeString + ' IDENTITY (1, 1) NULL'
When (@column_id = 1 And @is_nullable = 'N' and @is_identity = 'Y') then 'Create Table ' + @table_name + '( [' + @column_name + @TypeString + ' IDENTITY (1, 1) NOT NULL'
When (@column_id = 1 And @is_nullable = 'Y' and @is_identity = 'N') then 'Create Table ' + @table_name + '( [' + @column_name + @TypeString + ' NULL'
When (@column_id = 1 And @is_nullable = 'N' and @is_identity = 'N') then 'Create Table ' + @table_name + '( [' + @column_name + @TypeString + ' NOT NULL'
When (@column_id > 1 and @column_id < @MaxID And @is_nullable = 'Y' and @is_identity = 'N') then ',[' + @column_name + @TypeString + ' NULL'
When (@column_id > 1 and @column_id < @MaxID And @is_nullable = 'N' and @is_identity = 'N') then ',[' + @column_name + @TypeString + ' NOT NULL'
When (@column_id > 1 and @column_id < @MaxID And @is_nullable = 'Y' and @is_identity = 'Y') then ',[' + @column_name + @TypeString + ' IDENTITY (1, 1) NULL'
When (@column_id > 1 and @column_id < @MaxID And @is_nullable = 'N' and @is_identity = 'Y') then ',[' + @column_name + @TypeString + ' IDENTITY (1, 1) NOT NULL'
When (@column_id = @MaxID And @is_nullable = 'Y' and @is_identity = 'N') Then ',[' + @column_name + @TypeString + ' NULL '
When (@column_id = @MaxID And @is_nullable = 'N' and @is_identity = 'N') Then ',[' + @column_name + @TypeString + ' NOT NULL '
When (@column_id = @MaxID And @is_nullable = 'Y' and @is_identity = 'Y') Then ',[' + @column_name + @TypeString + ' IDENTITY (1, 1) NULL '
When (@column_id = @MaxID And @is_nullable = 'N' and @is_identity = 'Y') Then ',[' + @column_name + @TypeString + ' IDENTITY (1, 1) NOT NULL '
End
If (@column_id = @MaxID)
Begin
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Table', @table_name , @Column_id + 1, ') On [' + IsNull(@Filegroup,'Primary') + ']' + CHAR(10) + CHAR(13) +' '
End
fetch next
from TableList
into @table_name , @Column_id, @column_name,@data_type,@length,@prec,@scale,@is_nullable,@is_identity,@Filegroup
end
close TableList
deallocate TableList
Insert Into #Script ( [Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'Table','Create','100','Go'
--And the indexes now (Primary Keys)
declare IndexList cursor scroll for
select distinct table_name ,constraint_name, pos, column_name,data_type,length,prec,scale,is_nullable,is_identity,[Filegroup]
from #BuildLines
Where Object_Type = 'PriKey'
order by constraint_name, pos
open IndexList
fetch first
from IndexList
into @table_name , @constraint_name, @Column_id, @column_name,@data_type,@length,@prec,@scale,@is_nullable,@is_identity,@Filegroup
while @@fetch_status <> -1
begin
Select @MaxId = Max(pos) from #BuildLines
Where constraint_name = @constraint_name
Group By constraint_name
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
Select 'Create Primary Key', @table_name , @Column_id,
Case
When (@column_id = 1) then 'ALTER TABLE [' + @table_name + '] ADD CONSTRAINT [' + @constraint_name + '] PRIMARY KEY CLUSTERED ( [' + @column_name + ']'
When (@column_id > 1 ) then ',[' + @column_name + '] '
End
If (@column_id = @MaxID)
Begin
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Primary Key', @table_name , @Column_id + 1, ' ) On [' + IsNull(@Filegroup,'Primary') + ']' + CHAR(10) + CHAR(13) +' '
End
fetch next
from IndexList
into @table_name , @constraint_name, @Column_id, @column_name,@data_type,@length,@prec,@scale,@is_nullable,@is_identity,@Filegroup
end
Close IndexList
deallocate IndexList
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'PriKey','Create','150','Go'
--And the indexes now (Check Constraints)
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Check Constraint', table_name , Column_id,
'ALTER TABLE [' + table_name + '] ADD CONSTRAINT [' + constraint_name + '] Check ' + condition + '' + CHAR(10) + CHAR(13) +' '
from #BuildLines
Where Object_Type = 'ChkCon'
order by constraint_name, pos
--And the indexes now (Foreign Keys)
declare IndexList cursor scroll for
select distinct Parent_Table , constraint_name, Child_Table, Child_column, Parent_Table, Parent_column, Pos
from #BuildLines
Where Object_Type = 'ForKey'
order by constraint_name, pos
open IndexList
fetch first
from IndexList
into @table_name , @constraint_name, @Child_Table, @Child_column, @Parent_Table, @Parent_column, @Pos
while @@fetch_status <> -1
begin
Select @MaxId = Max(pos) from #BuildLines
Where constraint_name = @constraint_name
Group By constraint_name
If @Pos = 1
Begin
Set @Header = 'ALTER TABLE ' + @Child_Table + ' WITH NOCHECK ADD CONSTRAINT '
Set @List1 = ' ' + @Child_column + ''
Set @Mid = ') REFERENCES ' + @Parent_Table
Set @List2 = ' ' + @Parent_column + ''
Set @Trailer = ') '
end
If @Pos > 1
Begin
Set @List1 = @List1 + ', ' + @Child_column + ''
Set @List2 = @List2 + ', ' + @Parent_column + ''
End
If @Pos = @MaxId
Begin
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Foreign Key', @table_name , @Pos + 1, @Header
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Foreign Key', @table_name , @Pos + 2, @constraint_name + ' FOREIGN KEY '
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Foreign Key', @table_name , @Pos + 3, '('
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Foreign Key', @table_name , @Pos + 4, @List1
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] , [Text]
 
Select 'Create Foreign Key', @table_name , @Pos + 5, @Mid
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] , [Text]  
Select 'Create Foreign Key', @table_name , @Pos + 6, '('
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Foreign Key', @table_name , @Pos + 7, @List2
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Foreign Key', @table_name , @Pos + 8, @Trailer
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
 
Select 'Create Foreign Key', @table_name , @Pos + 8, 'GO'
End
/*
ALTER TABLE dbo.TRACKING_QUEUE WITH NOCHECK ADD CONSTRAINT
R_ORDINFO_TRKQUE FOREIGN KEY
(
TRACKING_NBR
 
REFERENCES dbo.ORDER_INFO(
TRACKING_NBR
 
*/
fetch next
from IndexList
into @table_name , @constraint_name, @Child_Table, @Child_column, @Parent_Table, @Parent_column, @Pos
end
Close IndexList
deallocate IndexList
Insert Into #Script (
[Object_Type],
[Object_name],
[pos] ,
[Text]
)
select 'ForKey','Create','250','Go'
--OK, ready for the procs
Declare ProcList Cursor scroll For
Select Distinct [Name] From mvdb.dbo.Object_matrix Where [required] = 'Y' and [DepFor] = 'StoreProcedure'
open ProcList
fetch first
from ProcList
into @Object_name
while @@fetch_status <> -1
begin
Set @SQLTxt = 'sp_helptext ' + @Object_name
Insert Into #Script (
[Text]
 
Select 'SET QUOTED_IDENTIFIER OFF '
Insert Into #Script (
[Text]
 
Select 'GO'
Insert Into #Script (
[Text]
 
Select 'SET ANSI_NULLS OFF'
Insert Into #Script (
[Text]
 
Select 'GO'
Insert Into #Script (
[Text]
 
Exec (@SQLTxt)
Insert Into #Script (
[Text]
 
Select 'GO'
Insert Into #Script (
[Text]
 
Select 'SET QUOTED_IDENTIFIER ON '
Insert Into #Script (
[Text]
 
Select 'GO'
Insert Into #Script (
[Text]
 
Select 'SET ANSI_NULLS ON'
Insert Into #Script (
[Text]
 
Select 'GO'
fetch next
from ProcList
into @Object_name
END
Close ProcList
Deallocate ProcList
--Select * from #Buildlines debug
Select * from #Script
order by ID
Set Nocount off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply