SPGen - Stored Procedure Generator

set nocount on

-- We verify that the tables, in which we will store the list of the objects to dynamically create, exist
if not exists (select * from dbo.sysobjects where id = object_id(N'dbo.#TableGenSP') and OBJECTPROPERTY(id, N'IsTable') = 1)
begin
	create table #TableGenSP (
		tbl_order int identity (1000, 100),
		tbl_name sysname not null,
		tbl_id int not null
	);

	create table #TempOrder (
		ftbl int not null,
		rtbl int not null,
		rorder int null
	);
end
else
begin
	truncate table #TableGenSP
	truncate table #TempOrder
end

-- Then we add the temp-procedure specific to keep user-modified statements (inside specific tags)
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.#usp_SaveUserCode') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure #usp_SaveUserCode
go
create procedure #usp_SaveUserCode (@FileID int, @mark varchar(128), @tabcount tinyint = 1)
as
	declare @start varchar(256)
	declare @end varchar(256)
	declare @tab char
	declare @temp varchar(1024)
	declare @command varchar(4000)
	declare @found tinyint
	declare @written tinyint
	DECLARE @OLEResult INT
	DECLARE cursSP CURSOR READ_ONLY FOR SELECT TMPSP_TEXT FROM #TEMPSP ORDER BY TMPSP_ID

	select @tab = char(9)
	select @start = '--' + @mark + ' START'
	select @end = '--' + @mark + ' END'

	select @found = 0
	select @written = 0
	open cursSP
	fetch next from cursSP into @temp
	while @@fetch_status = 0
	begin
		if patindex ('%' + @start + '%', @temp) <> 0
		begin
			select @found = 1
			select @temp = replace (replace (@temp , char(13), '') , char(10), '')
			EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
			select @written = 1
		end
		else if patindex ('%' + @end + '%', @temp) <> 0
		begin
			select @found = 0
			select @temp = replace (replace (@temp , char(13), '') , char(10), '')
			EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
			select @written = 2
		end
		else
		begin
			if @found = 1
			begin
				select @temp = replace (replace (@temp , char(13), '') , char(10), '')
				EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
			end
		end
		fetch next from cursSP into @temp
	end
	close cursSP
	deallocate cursSP

	if @written = 0
	begin
		select @temp = replicate (@tab, @tabcount) + @start
		EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
	end
	if @written <> 2
	begin
		select @temp = replicate (@tab, @tabcount) + @end
		EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @temp
	end
go

-- We insert first all the objects which are not referencing something else using a foreign key
insert into #TableGenSP (tbl_name, tbl_id) 
	select name, id
	from sysobjects 
	where xtype = 'U' and name not in ('dtproperties', 'TableGenSp', 'TempOrder') and id not in (select parent_obj from sysobjects where xtype = 'F')

-- We insert then all the foreign references
insert into #TempOrder (ftbl, rtbl) 
	select fkeyid, rkeyid from sysreferences 
	where fkeyid in (
		select parent_obj from sysobjects where xtype = 'F' and parent_obj in (select fkeyid from sysreferences)
		and	parent_obj not in (select tbl_id from #TableGenSP))

-- Now we have to update our two tables by using an update process ::
-- We are adding tables as long as their foreign dependencies can be resolved. 
-- This is possible if the datamodel is well built (ie. no circular dependencies between tables)
while exists (select * from #TempOrder where rorder is null)
begin
	update #TempOrder set rorder = tbl_order from #TableGenSP inner join #TempOrder on rtbl = tbl_id and rorder is null
	insert into #TableGenSP (tbl_name, tbl_id) 
		select name, id from sysobjects
		where id in (select ftbl from #TempOrder where ftbl not in (select distinct ftbl from #TempOrder where rorder is null))
			and id not in (select tbl_id from #TableGenSP)
end

declare @tempdir varchar(260)
declare @fname varchar(260)
declare @spname sysname
declare @svrname sysname
declare @dbosql sysname
declare @imp_user sysname
declare @table sysname
declare @name sysname
declare @col sysname
declare @xtype int
declare @coltype sysname
declare @length int
declare @id numeric
declare @SelKey varchar(2048)
declare @command varchar(4000)
declare @var varchar(255)
declare @cols varchar(4000)
declare @vars varchar(4000)
declare @crlf varchar(2)
declare @tab char
declare @first tinyint
DECLARE @FS INT, @OLEResult INT, @FileID INT

declare cursTable cursor read_only for select tbl_name, tbl_id from #TableGenSP order by tbl_order

select @tempdir = 'f:\temp'
select @svrname = 'arcturus'
select @dbosql = 'fineurope'
select @imp_user = 'fineurope'
select @crlf = char(13) + char(10)
select @tab = char(9)

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

open cursTable
fetch next from cursTable into @table, @id
while @@fetch_status = 0
begin
	select @spname = 'usp_' + @table
	select @fname = @tempdir + '\' + @spname + '.sql'

	CREATE TABLE #TEMPSP (TMPSP_ID int IDENTITY, TMPSP_TEXT varchar(1024))
	INSERT INTO #TEMPSP EXEC sp_helptext @SPNAME
	
	EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @fname, 2, 1
	IF @OLEResult <> 0 PRINT 'OpenTextFile'

	select @command = 'if exists (select * from dbo.sysobjects where id = object_id(N''dbo.' + @spname + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)' + @CRLF
	select @command = @command + 'drop procedure dbo.' + @spname + @crlf
	exec (@command)

	select @command = 'CREATE PROCEDURE dbo.' + @spname  + '(@action char'

	select @vars = ''
	declare cursCols cursor read_only for 
		select sc.name, sc.xtype, st.name, sc.length from syscolumns sc 
			inner join systypes st on sc.xusertype = st.xusertype 
		where id = @id and sc.xusertype not in (34, 35, 99) 
			-- We are adding this, since with our conventions column's name containing 
			-- DATECREATED or LASTUPDATE have values only from DEFAULT or trigger
			and patindex ('%datecreated', sc.name) = 0 and patindex ('%lastupdate', sc.name) = 0 
		order by colid

	open cursCols
	fetch next from cursCols into @col, @xtype, @coltype, @length
	while @@fetch_status = 0
	begin
		select @col = replace (@col, ' ', '_')
		select @var = @col + ' ' + @coltype
		if @xtype in (167, 175)
		begin
			select @var = @var + '(' + convert (varchar(16), @length) + ')'
		end
		-- We must deal with tne N types differently
		else if @xtype in (231, 239)
		begin
			select @var = @var + '(' + convert (varchar(16), @length / 2) + ')'
		end

		select @command = @command + ', @' + @var + ' = null'
		select @vars = @vars + @tab + 'declare @old_' + @var + @crlf
		fetch next from cursCols into @col, @xtype, @coltype, @length
	end
	close cursCols
	deallocate cursCols
	select @command = @command + ')' + @crlf
	select @command = @command + 'as' + @crlf
	select @command = @command + @tab + 'set nocount on' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
	select @command = @vars
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	select @command = ''
	-- First We build the WHERE clause for all requests
	select @first = 1
	select @SelKey = ''
	declare cursKey cursor read_only for select name from syscolumns sc inner join
		(select sik.id, sik.colid from sysindexkeys sik inner join sysindexes six on sik.id = six.id and sik.indid = six.indid
			inner join sysobjects so on six.name = so.name and so.xtype = 'PK' and six.id = @id) t
		on sc.id = t.id and sc.colid = t.colid
	open cursKey
	fetch next from cursKey into @name
	while @@fetch_status = 0
	begin
		if @first = 0
		begin
			select @SelKey = @SelKey + ' AND '
		end
		select @first = 0
		select @SelKey = @SelKey + '[' + @name + '] = @' + replace (@name, ' ', '_')
		fetch next from cursKey into @name
	end
	close cursKey
	deallocate cursKey

	exec #usp_SaveUserCode @FileID, 'EnterSP', 1
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, ''

	-- First action is DELETE
	select @command = @tab + 'if @action = ''D''' + @crlf
	select @command = @command + @tab + 'begin'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	exec #usp_SaveUserCode @FileID, 'Before DELETE', 1
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, ''

	select @command = @tab + @tab + 'DELETE FROM [' + @table + '] WHERE ' + @SelKey + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	exec #usp_SaveUserCode @FileID, 'After DELETE', 2

	select @command = @tab + 'end'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	select @command = @tab + 'else if @action = ''U''' + @crlf
	select @command = @command + @tab + 'begin' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	exec #usp_SaveUserCode @FileID, 'Before UPDATE', 2
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, ''

	select @command = @tab + @tab + 'UPDATE [' + @table + '] SET'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	select @command = ''
	select @cols = ''
	select @first = 1
	declare cursCols cursor read_only for 
		select sc.name from syscolumns sc 
			inner join systypes st on sc.xtype = st.xtype 
		where id = @id and sc.autoval is null and sc.xtype not in (34, 35, 99)
			-- We are adding this, since with our conventions column's name containing 
			-- DATECREATED or LASTUPDATE have values only from DEFAULT or trigger
			and patindex ('%datecreated', sc.name) = 0 and patindex ('%lastupdate', sc.name) = 0 
		order by colid

	open cursCols
	fetch next from cursCols into @col
	while @@fetch_status = 0
	begin
		select @command = @command + @tab + @tab + @tab
		if @first = 0
		begin
			select @command = @command + ', '
		end
		select @first = 0
		select @command = @command + '[' + @col + '] = @' + replace (@col, ' ', '_') + @crlf
		fetch next from cursCols into @col
	end
	close cursCols
	deallocate cursCols
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
	select @command = @tab + @tab + 'WHERE ' + @SelKey + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	exec #usp_SaveUserCode @FileID, 'After UPDATE', 2

	select @command = @tab + 'end'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	-- Then the INSERT
	select @command = @tab + 'else if @action = ''I''' + @crlf
	select @command = @command + @tab + 'begin'
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	select @command = ''
	select @cols = ''
	select @vars = ''
	select @first = 1
	declare cursCols cursor read_only for 
		select sc.name from syscolumns sc 
			inner join systypes st on sc.xtype = st.xtype 
			where id = @id and sc.autoval is null and sc.xtype not in (34, 35, 99)
			-- We are adding this, since with our conventions column's name containing 
			-- DATECREATED or LASTUPDATE have values only from DEFAULT or trigger
			and patindex ('%datecreated', sc.name) = 0 and patindex ('%lastupdate', sc.name) = 0 
		order by colid

	open cursCols
	fetch next from cursCols into @col
	while @@fetch_status = 0
	begin
		if @first = 0
		begin
			select @cols = @cols + ', '
			select @vars = @vars + ', '
		end
		select @first = 0
		select @cols = @cols + '[' + @col + ']'
		select @vars = @vars + '@' + replace (@col, ' ', '_')
		fetch next from cursCols into @col
	end
	close cursCols
	deallocate cursCols

	exec #usp_SaveUserCode @FileID, 'Before INSERT', 2
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, ''

	select @command = @tab + @tab + 'INSERT INTO [' + @table + ']' + @crlf
	select @command = @command + @tab + @tab + @tab + '(' + @cols + ')' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command
	select @command = @tab + @tab + 'VALUES' + @crlf
	select @command = @command + @tab + @tab + @tab + '(' + @vars + ')' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	exec #usp_SaveUserCode @FileID, 'After INSERT', 2

	select @command = @tab + 'end' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	exec #usp_SaveUserCode @FileID, 'ExitSP', 1

	select @command = 'go' + @crlf
	EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @command

	EXECUTE @OLEResult = sp_OADestroy @FileID

	SELECT @command = 'osql.exe -S' + @SVRNAME + ' -U' + @IMP_USER + ' -P' + @IMP_USER + ' -d' + @DBOSQL + ' -i' + @FNAME + ' -n'
	EXEC master..xp_cmdshell @command

	if not exists (select * from dbo.sysobjects where id = object_id(N'dbo.' + @SPNAME) and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN
		print 'Error creating : ' + @spname
	END
	ELSE
	BEGIN
		EXECUTE @OLEResult = sp_OAMethod @FS, 'DeleteFile', NULL, @FNAME, -1
		IF @OLEResult <> 0 PRINT 'WriteLine : DeleteFile ' + @FNAME + ' error ' + convert (varchar(32), @OleResult)
	END
	fetch next from cursTable into @table, @id

	DROP TABLE #TEMPSP
end
close cursTable
deallocate cursTable

-- Our job is over, we can clean everything
EXECUTE @OLEResult = sp_OADestroy @FS

drop table #TableGenSP
drop table #TempOrder
drop procedure #usp_SaveUserCode

GlobalSection(ProjectConfiguration) = postSolution
	{87ADD9EE-AA83-45B8-9BD8-4EC5FC26CCAB}.Debug.ActiveCfg = Release|.NET
	{87ADD9EE-AA83-45B8-9BD8-4EC5FC26CCAB}.Debug.Build.0 = Release|.NET
	{87ADD9EE-AA83-45B8-9BD8-4EC5FC26CCAB}.Release.ActiveCfg = Release|.NET
	{87ADD9EE-AA83-45B8-9BD8-4EC5FC26CCAB}.Release.Build.0 = Release|.NET