|
||
| Pearson Technology Consulting | ||
|
P_PTC_SQLGEN_MSSQL2000
Back to SQL Generator CREATE PROCEDURE P_PTC_SQLGEN_MSSQL2000 (
@table_name nvarchar(384),
@table_owner nvarchar(384) = 'dbo',
@sproc_owner nvarchar(384) = 'dbo') AS
/*-----------------------------------------------------------------------*/
/* (c) Pearson Technology Consulting Incorporated - All rights reserved. */
/*-----------------------------------------------------------------------*/
/* */
/* A utility for generating basic SQL statements and stored procedures. */
/* */
/* Pass a table name and receive multiple result sets containing SQL for */
/* inserting, selecting, updating and deleting rows from that table. */
/* */
/* Feel free to modify to fit your needs but please give credit in this */
/* comment block to Pearson Technology Consulting Incorporated. */
/* */
/* Use at your own risk (but you know that this warning applies to all */
/* resources you download from the internet or receive in email). */
/* */
/* See http://www.pearsontechnology.com to send feedback or comments. */
/* */
/*-----------------------------------------------------------------------*/
SET NOCOUNT ON
DECLARE @tableid int, @full_table_name nvarchar(769)
SET @full_table_name = quotename(@table_owner) + '.' + quotename(@table_name)
SET @tableid = object_id(@full_table_name)
IF @tableid IS NULL BEGIN
SELECT 'Table not found!' AS "Error"
RETURN
END
DECLARE @TblCol TABLE (
colname sysname not null,
typname sysname not null,
precis int not null,
scale int null,
colseq int not null,
idcol int not null,
fulltype sysname not null)
INSERT @TblCol
/*------------------------------------*/
/* BEGIN... Distilled from sp_columns */
/*------------------------------------*/
SELECT
convert(sysname,c.name),
convert(sysname,case when t.xusertype > 255 then t.name else d.TYPE_NAME collate database_default end),
convert(int,case when d.DATA_TYPE in (6,7) then d.data_precision else OdbcPrec(c.xtype,c.length,c.xprec) end),
convert(smallint, OdbcScale(c.xtype,c.xscale)),
convert(int,(select count(*) from syscolumns sc where sc.id = c.id AND sc.number = c.number AND sc.colid <= c.colid)),
0,
''
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
WHERE
o.id = @tableid
AND c.id = o.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = 2)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty(c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
ORDER BY
5
/*----------------------------------*/
/* END... Distilled from sp_columns */
/*----------------------------------*/
UPDATE @TblCol SET typname = Substring(typname, 1, CharIndex('identity', typname) - 2), idcol = 1 WHERE CharIndex('identity', typname) > 0
UPDATE @TblCol SET fulltype =
CASE
WHEN typname IN ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') THEN typname + '(' + Cast(precis AS varchar(10)) + ')'
WHEN typname IN ('decimal', 'numeric') THEN typname + '(' + Cast(precis AS varchar(10)) + ',' + Cast(scale AS varchar(10)) + ')'
ELSE typname
END
DECLARE @TblKey TABLE (
colname sysname not null,
keyseq int not null)
INSERT @TblKey
/*----------------------------------*/
/* BEGIN... Distilled from sp_pkeys */
/*----------------------------------*/
select
convert(sysname,c.name),
case
when c.name = index_col(@full_table_name, i.indid, 1) then convert (smallint,1)
when c.name = index_col(@full_table_name, i.indid, 2) then convert (smallint,2)
when c.name = index_col(@full_table_name, i.indid, 3) then convert (smallint,3)
when c.name = index_col(@full_table_name, i.indid, 4) then convert (smallint,4)
when c.name = index_col(@full_table_name, i.indid, 5) then convert (smallint,5)
when c.name = index_col(@full_table_name, i.indid, 6) then convert (smallint,6)
when c.name = index_col(@full_table_name, i.indid, 7) then convert (smallint,7)
when c.name = index_col(@full_table_name, i.indid, 8) then convert (smallint,8)
when c.name = index_col(@full_table_name, i.indid, 9) then convert (smallint,9)
when c.name = index_col(@full_table_name, i.indid, 10) then convert (smallint,10)
when c.name = index_col(@full_table_name, i.indid, 11) then convert (smallint,11)
when c.name = index_col(@full_table_name, i.indid, 12) then convert (smallint,12)
when c.name = index_col(@full_table_name, i.indid, 13) then convert (smallint,13)
when c.name = index_col(@full_table_name, i.indid, 14) then convert (smallint,14)
when c.name = index_col(@full_table_name, i.indid, 15) then convert (smallint,15)
when c.name = index_col(@full_table_name, i.indid, 16) then convert (smallint,16)
end
from
sysindexes i, syscolumns c, sysobjects o
where
o.id = @tableid
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
and (
c.name = index_col (@full_table_name, i.indid, 1) or
c.name = index_col (@full_table_name, i.indid, 2) or
c.name = index_col (@full_table_name, i.indid, 3) or
c.name = index_col (@full_table_name, i.indid, 4) or
c.name = index_col (@full_table_name, i.indid, 5) or
c.name = index_col (@full_table_name, i.indid, 6) or
c.name = index_col (@full_table_name, i.indid, 7) or
c.name = index_col (@full_table_name, i.indid, 8) or
c.name = index_col (@full_table_name, i.indid, 9) or
c.name = index_col (@full_table_name, i.indid, 10) or
c.name = index_col (@full_table_name, i.indid, 11) or
c.name = index_col (@full_table_name, i.indid, 12) or
c.name = index_col (@full_table_name, i.indid, 13) or
c.name = index_col (@full_table_name, i.indid, 14) or
c.name = index_col (@full_table_name, i.indid, 15) or
c.name = index_col (@full_table_name, i.indid, 16))
order by
2
/*--------------------------------*/
/* END... Distilled from sp_pkeys */
/*--------------------------------*/
DECLARE @SqlGen TABLE (
id int Identity(1,1) not null,
sqltype int not null,
sqlline varchar(200) not null)
DECLARE @sql_table_name nvarchar(384)
DECLARE @idcol sysname
DECLARE @type int
SET @full_table_name = quotename(@sproc_owner) + '.' + quotename(@table_name)
SET @tableid = object_id(@full_table_name)
SET @sql_table_name = CASE WHEN @table_owner = @sproc_owner OR @table_owner = 'dbo' AND @tableid IS NULL THEN '' WHEN @table_owner = 'dbo' THEN 'dbo.' ELSE '[' + @table_owner + '].' END + @table_name
SET @type = 1
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'INSERT ' + @sql_table_name + ' (')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' ' + colname + ',' FROM @TblCol WHERE idcol = 0 AND fulltype <> 'timestamp' ORDER BY colseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 1) + ')' WHERE id = @@IDENTITY
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'VALUES (')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' @' + colname + ',' FROM @TblCol WHERE idcol = 0 AND fulltype <> 'timestamp' ORDER BY colseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 1) + ')' WHERE id = @@IDENTITY
SET @type = 2
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'SELECT')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' A.' + colname + ',' FROM @TblCol ORDER BY colseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 1) WHERE id = @@IDENTITY
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'FROM')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, ' ' + @sql_table_name + ' AS A')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'WHERE')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' A.' + colname + ' = @' + colname + ' AND' FROM @TblKey ORDER BY keyseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 4) WHERE id = @@IDENTITY
SET @type = 3
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'UPDATE ' + @sql_table_name + ' SET')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' ' + colname + ' = @' + colname + ',' FROM @TblCol WHERE colname NOT IN (SELECT colname FROM @TblKey) AND fulltype <> 'timestamp' ORDER BY colseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 1) WHERE id = @@IDENTITY
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'WHERE')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' ' + colname + ' = @' + colname + ' AND' FROM @TblKey ORDER BY keyseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 4) WHERE id = @@IDENTITY
SET @type = 4
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'DELETE')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, ' ' + @sql_table_name)
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'WHERE')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' ' + colname + ' = @' + colname + ' AND' FROM @TblKey ORDER BY keyseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 4) WHERE id = @@IDENTITY
IF @sproc_owner <> 'dbo' SET @sproc_owner = '[' + @sproc_owner + ']'
SET @type = 11
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'if exists (select * from dbo.sysobjects where id = object_id(N''' + @sproc_owner + '.[P_' + @table_name + '_I1]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'drop procedure ' + @sproc_owner + '.[P_' + @table_name + '_I1]')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'GO')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'CREATE PROCEDURE ' + @sproc_owner + '.P_' + @table_name + '_I1 (')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' @' + colname + ' ' + fulltype + CASE WHEN idcol = 0 THEN '' ELSE ' OUTPUT' END + ',' FROM @TblCol WHERE fulltype <> 'timestamp' ORDER BY idcol, colseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 1) + ') AS' WHERE id = @@IDENTITY
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'DECLARE @return_code int')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, sqlline FROM @SqlGen WHERE sqltype = 1 ORDER BY id
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
SELECT @idcol = colname FROM @TblCol WHERE idcol > 0
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'SELECT @return_code = @@ERROR' + CASE WHEN @idcol IS NULL THEN '' ELSE ', @' + @idcol + ' = @@IDENTITY' END)
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'RETURN @return_code')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'GO')
SET @type = 12
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'if exists (select * from dbo.sysobjects where id = object_id(N''' + @sproc_owner + '.[P_' + @table_name + '_S1]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'drop procedure ' + @sproc_owner + '.[P_' + @table_name + '_S1]')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'GO')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'CREATE PROCEDURE ' + @sproc_owner + '.P_' + @table_name + '_S1 (')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' @' + A.colname + ' ' + A.fulltype + ',' FROM @TblCol AS A JOIN @TblKey AS B ON A.colname = B.colname ORDER BY keyseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 1) + ') AS' WHERE id = @@IDENTITY
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'DECLARE @return_code int')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, sqlline FROM @SqlGen WHERE sqltype = 2 ORDER BY id
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'SELECT @return_code = @@ERROR')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'RETURN @return_code')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'GO')
SET @type = 13
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'if exists (select * from dbo.sysobjects where id = object_id(N''' + @sproc_owner + '.[P_' + @table_name + '_U1]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'drop procedure ' + @sproc_owner + '.[P_' + @table_name + '_U1]')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'GO')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'CREATE PROCEDURE ' + @sproc_owner + '.P_' + @table_name + '_U1 (')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' @' + colname + ' ' + fulltype + ',' FROM @TblCol WHERE fulltype <> 'timestamp' ORDER BY colseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 1) + ') AS' WHERE id = @@IDENTITY
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'DECLARE @return_code int')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, sqlline FROM @SqlGen WHERE sqltype = 3 ORDER BY id
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'SELECT @return_code = @@ERROR')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'RETURN @return_code')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'GO')
SET @type = 14
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'if exists (select * from dbo.sysobjects where id = object_id(N''' + @sproc_owner + '.[P_' + @table_name + '_D1]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'drop procedure ' + @sproc_owner + '.[P_' + @table_name + '_D1]')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'GO')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'CREATE PROCEDURE ' + @sproc_owner + '.P_' + @table_name + '_D1 (')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, ' @' + A.colname + ' ' + A.fulltype + ',' FROM @TblCol AS A JOIN @TblKey AS B ON A.colname = B.colname ORDER BY keyseq
UPDATE @SqlGen SET sqlline = Substring(sqlline, 1, Len(sqlline) - 1) + ') AS' WHERE id = @@IDENTITY
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'DECLARE @return_code int')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) SELECT @type, sqlline FROM @SqlGen WHERE sqltype = 4 ORDER BY id
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'SELECT @return_code = @@ERROR')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, '')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'RETURN @return_code')
INSERT @SqlGen (sqltype, sqlline) VALUES (@type, 'GO')
SELECT sqlline AS 'Insert SQL' FROM @SqlGen WHERE sqltype = 1 ORDER BY id
SELECT sqlline AS 'Select SQL' FROM @SqlGen WHERE sqltype = 2 ORDER BY id
SELECT sqlline AS 'Update SQL' FROM @SqlGen WHERE sqltype = 3 ORDER BY id
SELECT sqlline AS 'Delete SQL' FROM @SqlGen WHERE sqltype = 4 ORDER BY id
SELECT sqlline AS 'Stored Procedure SQL' FROM @SqlGen WHERE sqltype IN (11,12,13,14) ORDER BY id
Back to SQL Generator |