----------------------------------------------------- Stored procedure: sp_GenerateQuery ----------------------------------------------------- USE master GO CREATE PROCEDURE dbo.sp_GenerateQuery @DBTable varchar(100), @PCAdmin varchar(100) = NULL, @DBAdmin int = 0, @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL AS SET NOCOUNT ON DECLARE @Return int DECLARE @Retain int DECLARE @Status int SET @Status = 0 DECLARE @TPre varchar(10) DECLARE @TDo3 tinyint DECLARE @TDo4 tinyint SET @TPre = '' SET @TDo3 = LEN(@TPre) SET @TDo4 = LEN(@TPre) + 1 DECLARE @Task varchar(8000) DECLARE @Bank varchar(8000) DECLARE @Cash varchar(8000) DECLARE @Rich varchar(4000) DECLARE @Name varchar(100) DECLARE @Same varchar(100) DECLARE @Tame varchar(100) DECLARE @Rank smallint DECLARE @Kind varchar(20) DECLARE @Mask bit DECLARE @Bond bit DECLARE @Size int DECLARE @Wide smallint DECLARE @More smallint DECLARE @Came varchar(100) DECLARE @DBAI varchar(8000) DECLARE @DBAO varchar(8000) DECLARE @DBAU varchar(8000) DECLARE @Main varchar(90) DECLARE @Wink varchar(50) DECLARE @Wish varchar(10) DECLARE @Work varchar(100) DECLARE @Save int DECLARE @Sack int DECLARE @Sick int DECLARE @Sock int DECLARE @Date varchar(8) DECLARE @Time varchar(8) SET @Date = CONVERT(varchar(8),GETDATE(),1) SET @Time = CONVERT(varchar(8),GETDATE(),8) CREATE TABLE #DBAZ (Name varchar(100)) CREATE TABLE #DBAT (Name varchar(100), Same varchar(100), Rank smallint, Kind varchar(20), Mask bit, Bond bit, Size int, Wide smallint, More smallint, Came varchar(100)) SET @Name = @DBTable IF NOT EXISTS (SELECT * FROM sysobjects WHERE type IN ('V ','IF','TF') AND name = @Name) BEGIN SET @Name = @TPre + @DBTable END INSERT #DBAT SELECT O.name , C.name , C.colid , T.name , C.isnullable , C.iscomputed , C.length , C.prec , C.scale , K.name FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id JOIN systypes AS T ON C.xusertype = T.xusertype LEFT JOIN sysreferences AS R ON O.id = R.fkeyid AND C.colid = R.fkey1 LEFT JOIN sysobjects AS K ON R.rkeyid = K.id WHERE O.name = @Name AND ASCII(LEFT(C.name,1)) > 64 SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain SELECT @Tame = Same FROM #DBAT WHERE Rank = 1 SELECT @Sack = MAX(Rank) FROM #DBAT SET @Task = '' SET @Bank = '' SET @Cash = '' SET @Rich = '' SET @DBAI = '' SET @DBAO = '' SET @DBAU = '' DECLARE Fields CURSOR FAST_FORWARD FOR SELECT Same, Rank, Kind, Mask, Bond, Size, Wide, More, Came FROM #DBAT ORDER BY Rank OPEN Fields FETCH NEXT FROM Fields INTO @Same, @Rank, @Kind, @Mask, @Bond, @Size, @Wide, @More, @Came WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @Work = CASE WHEN @Kind IN ('decimal','numeric') THEN @Kind + '(' + STR(@Wide,2) + ',' + STR(@More,2) + ')' WHEN @Kind IN ('float') THEN @Kind + '(' + STR(@Wide,2) + ')' WHEN @Kind IN ('char','varchar') THEN @Kind + '(' + STR(@Size,4) + ')' WHEN @Kind IN ('nchar','nvarchar') THEN @Kind + '(' + STR(@Size,4) + ')' WHEN @Kind IN ('binary','varbinary') THEN @Kind + '(' + STR(@Size,4) + ')' ELSE @Kind END SET @Save = 62 - LEN(@Same) - LEN(@Work) IF @Wink < 2 SET @Save = 72 - LEN(@Same) - LEN(@Work) IF @Wink < 2 SET @Save = 82 - LEN(@Same) - LEN(@Work) IF @Wink < 2 SET @Save = 02 SET @Work = SPACE(@Save) + @Work SET @Main = 'T01.' + @Same IF @Rank = 1 SET @DBAI = @DBAI + '* @' + @Same IF @Rank > 1 SET @DBAI = @DBAI + CHAR(13) + CHAR(10) + '*,@' + @Same IF @Rank = 1 SET @DBAO = @DBAO + 'DECLARE @' + @Same + @Work IF @Rank > 1 SET @DBAO = @DBAO + CHAR(13) + CHAR(10) + ' , @' + @Same + @Work IF @Rank = 1 SET @Bank = @Bank + ' ( ' + @Same + @Work IF @Rank > 1 SET @Bank = @Bank + CHAR(13) + CHAR(10) + ' , ' + @Same + @Work IF @Rank = 1 SET @Cash = @Cash + ' ( ' + @Same IF @Rank > 1 SET @Cash = @Cash + CHAR(13) + CHAR(10) + ' , ' + @Same IF @Rank = 1 SET @DBAU = @DBAU + @Main IF @Rank > 1 SET @DBAU = @DBAU + CHAR(13) + CHAR(10) + ' , ' + @Main IF @Rank > 1 AND @Came IS NOT NULL AND @DBAdmin = 1 BEGIN SET @Wish = 'T' + RIGHT(STR(@Rank+100,3),2) SET @Task = @Task + CHAR(13) + CHAR(10) + CASE WHEN @Mask = 0 THEN SPACE(4) ELSE 'LEFT' END + ' JOIN ' + @Came + ' AS ' + @Wish + CHAR(13) + CHAR(10) + SPACE(7) + 'ON ' + @Wish + '.' + @Same + CHAR(13) + CHAR(10) + SPACE(7) + ' = ' + @Main SET @Rich = @Rich + CHAR(13) + CHAR(10) + SPACE(7) + ' , ' + @Wish + '.' + '*' END FETCH NEXT FROM Fields INTO @Same, @Rank, @Kind, @Mask, @Bond, @Size, @Wide, @More, @Came END CLOSE Fields DEALLOCATE Fields SET @Bank = @Bank + ')' SET @Cash = @Cash + ')' SET @DBAU = @DBAU + @Rich IF @DBAdmin = 2 AND (SELECT COUNT(*) FROM #DBAT WHERE Came IS NOT NULL) > 0 BEGIN INSERT #DBAT SELECT O.name , C.name , C.colid , T.name , C.isnullable , C.iscomputed , C.length , C.prec , C.scale , K.name FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id JOIN systypes AS T ON C.xusertype = T.xusertype LEFT JOIN sysreferences AS R ON O.id = R.fkeyid AND C.colid = R.fkey1 LEFT JOIN sysobjects AS K ON R.rkeyid = K.id WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0 AND RTRIM(O.type) = 'U' AND LEFT(O.name,@TDo3) = @TPre AND O.name NOT LIKE 'adt%' AND O.name NOT LIKE '%dtproper%' AND O.name NOT LIKE 'dt[_]%' AND (C.colid = 1 OR K.name IS NOT NULL) AND (O.name <> @Name) AND (@DBIntra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBIntra)+'|') > 0) AND (@DBExtra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBExtra)+'|') = 0) AND (@PCIntra IS NULL OR SUBSTRING(O.name,@TDo4,100) LIKE @PCIntra) AND (@PCExtra IS NULL OR SUBSTRING(O.name,@TDo4,100) NOT LIKE @PCExtra) SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain SELECT @Save = COUNT(*) FROM #DBAT WHERE Rank = 1 IF @Save + @Sack > 100 BEGIN RAISERROR ('Included tables [%d] and fields [%d] must total less than 100.',16,1,@Save,@Sack) PRINT SPACE(0) PRINT SPACE(0) SET @Status = 106 END SET @Sick = 1 SET @Wish = 'T01' UPDATE #DBAT SET More = 0, Kind = NULL UPDATE #DBAT SET More = 1, Kind = @Wish WHERE Name = @Name INSERT #DBAZ SELECT DISTINCT Came FROM #DBAT WHERE Came IS NOT NULL DELETE #DBAT WHERE More = 0 AND Name NOT IN (SELECT * FROM #DBAZ) WHILE (SELECT COUNT(*) FROM #DBAT WHERE More = 0) > 0 AND @Status = 0 BEGIN UPDATE #DBAT SET More = @Sick + 1 WHERE Name IN (SELECT DISTINCT Came FROM #DBAT WHERE More = @Sick) SET @Save = @@ROWCOUNT IF @Save = 0 BEGIN DELETE #DBAT WHERE More = 0 END ELSE BEGIN SET @Sick = @Sick + 1 DECLARE Tables CURSOR FAST_FORWARD FOR SELECT Name, Same FROM #DBAT WHERE Rank = 1 AND More = @Sick AND Kind IS NULL ORDER BY Name OPEN Tables FETCH NEXT FROM Tables INTO @Name, @Tame WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @Sack = @Sack + 1 SET @Wish = 'T' + RIGHT(STR(@Sack+100,3),2) UPDATE #DBAT SET Kind = @Wish WHERE Name = @Name SET @Mask = 0 SELECT TOP 1 @Mask = Mask FROM #DBAT WHERE Came = @Name AND More > 0 AND Mask <> 0 ORDER BY Name UPDATE #DBAT SET Mask = @Mask WHERE Name = @Name SET @Task = @Task + CHAR(13) + CHAR(10) + CASE WHEN @Mask = 0 THEN SPACE(4) ELSE 'LEFT' END + ' JOIN ' + @Name + ' AS ' + @Wish SET @Sock = 0 DECLARE Tablez CURSOR FAST_FORWARD FOR SELECT Name, Kind FROM #DBAT WHERE Came = @Name AND More > 0 GROUP BY Name, Kind ORDER BY Kind OPEN Tablez FETCH NEXT FROM Tablez INTO @Same, @Kind WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @Sock = @Sock + 1 IF @Sock = 1 SET @Wink = ' ON ' IF @Sock > 1 SET @Wink = ' AND ' SET @Task = @Task + CHAR(13) + CHAR(10) + SPACE(5) + @Wink + @Wish + '.' + @Tame + CHAR(13) + CHAR(10) + SPACE(7) + ' = ' + @Kind + '.' + @Tame SET @DBAU = @DBAU + CHAR(13) + CHAR(10) + SPACE(7) + ' , ' + @Wish + '.' + '*' FETCH NEXT FROM Tablez INTO @Same, @Kind END CLOSE Tablez DEALLOCATE Tablez FETCH NEXT FROM Tables INTO @Name, @Tame END CLOSE Tables DEALLOCATE Tables END END SELECT @Name = Name, @Tame = Same FROM #DBAT WHERE Rank = 1 AND More = 1 END -- LEFT(@PCAdmin,1) = Anything BEGIN SET @Main = 'T01.' + @Tame PRINT '-- Generated by DBAction' PRINT '-- Generated on ' + @Date PRINT '-- Generated at ' + @Time PRINT SPACE(0) END IF LEFT(@PCAdmin,1) = '@' BEGIN PRINT 'DECLARE ' + @PCAdmin + ' TABLE' PRINT @Bank PRINT SPACE(0) PRINT ' INSERT ' + @PCAdmin PRINT @Cash END IF LEFT(@PCAdmin,1) = '#' BEGIN PRINT 'CREATE TABLE ' + @PCAdmin PRINT @Bank PRINT SPACE(0) PRINT ' INSERT ' + @PCAdmin PRINT @Cash END IF LEFT(@PCAdmin,1) LIKE '[*|]' AND ISNULL(OBJECTPROPERTY(OBJECT_ID(@Name),'TableHasIdentity'),0) <> 0 BEGIN PRINT '-- SET IDENTITY_INSERT ' + @Name + ' ON ' PRINT SPACE(0) END IF LEFT(@PCAdmin,1) LIKE '[*|]' BEGIN PRINT ' INSERT ' + @Name PRINT @Cash END IF LEFT(@PCAdmin,1) NOT LIKE '[*|@#]' BEGIN PRINT 'DECLARE @Return int' PRINT 'DECLARE @Retain int' PRINT 'DECLARE @Status int' PRINT SPACE(0) PRINT 'SET @Status = 0' PRINT SPACE(0) PRINT @DBAO PRINT SPACE(0) PRINT ' DECLARE ' + @PCAdmin + ' CURSOR FAST_FORWARD FOR' END -- LEFT(@PCAdmin,1) = Anything BEGIN PRINT ' SELECT ' + @DBAU PRINT ' FROM ' + @Name + ' AS T01 -- WITH (NOLOCK)' + @Task PRINT ' WHERE ' + @Main PRINT ' = ' + @Main END IF LEFT(@PCAdmin,1) = '#' BEGIN PRINT SPACE(0) PRINT 'DROP TABLE ' + @PCAdmin END IF LEFT(@PCAdmin,1) LIKE '[*|]' AND ISNULL(OBJECTPROPERTY(OBJECT_ID(@Name),'TableHasIdentity'),0) <> 0 BEGIN PRINT SPACE(0) PRINT '-- SET IDENTITY_INSERT ' + @Name + ' OFF ' END IF LEFT(@PCAdmin,1) NOT LIKE '[*|@#]' BEGIN PRINT SPACE(0) PRINT 'SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain' PRINT SPACE(0) PRINT 'OPEN ' + @PCAdmin PRINT SPACE(0) PRINT 'SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain' PRINT SPACE(0) PRINT 'FETCH NEXT FROM ' + @PCAdmin + ' INTO' PRINT REPLACE(@DBAI,'*',SPACE(3)) PRINT SPACE(0) PRINT 'SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain' PRINT SPACE(0) PRINT 'WHILE @@FETCH_STATUS = 0 AND @Status = 0' PRINT SPACE(0) PRINT ' BEGIN' PRINT SPACE(0) PRINT ' FETCH NEXT FROM ' + @PCAdmin + ' INTO' PRINT REPLACE(@DBAI,'*',SPACE(7)) PRINT SPACE(0) PRINT ' SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain' PRINT SPACE(0) PRINT ' END' PRINT SPACE(0) PRINT 'CLOSE ' + @PCAdmin + ' DEALLOCATE ' + @PCAdmin END -- LEFT(@PCAdmin,1) = Anything BEGIN PRINT SPACE(0) END DROP TABLE #DBAZ DROP TABLE #DBAT SET NOCOUNT OFF RETURN (@Status) GO -----------------------------------------------------