You all have been through this at least once in your life. In your shop there is this one person who likes to use Enterprise Manager as their Rapid Data Entry Application. We all know how these people operate; they delete rows, drop tables and all kinds of other funky stuff. SQL Server 2005 has DDL triggers to help you protect against these scoundrels. What about if you are still running that piece of software from the late Triassic period known as SQL Server 2000, what can help you in that case? Don’t worry I will show you a way but first I will tell you a story. About 6 years ago I worked in New York City as a consultant on a project for a nonprofit organization. I looked in the database and found this table which was named YesNoTable. I was curious I opened the table and noticed it had only 2 rows. Here is what was stored in the table.
0 no
1 yes
I dropped it immediately. 5 minutes went by and suddenly the CRM application was broken. They ran the debugger and found out a table was missing. Luckily for me it was very easy to recreate this table. And yes, we did get rid of it soon after. Now had the table be used by a view which had been created with schemabinding I would not be able to drop the table without dropping the view first. You see even I became a SQL villain one time.
What the code below does is it will loop through all the user created tables then union them all, I created a where 1 =0 WHERE clause just in case someone decides to open the view. Since a union can only have 250 selects or so, I have created the code so that you can specify how many tables per view you would like, you do that with the @UnionCount variable.
The code does print statements it does not create the views
If you run the code in the msdb database and you specify 5 as the @UnionCount your output will be this
-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_1 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[log_shipping_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_monitor]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_databases]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plan_history]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[log_shipping_plans]
WHERE 1=0
GO
-- ****************************
-- **** View Starts Here *****
-- ****************************
CREATE VIEW DoNotDropMe_2 WITH SCHEMABINDING
AS
SELECT 1 As Col1 FROM [dbo].[RTblClassDefs]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDatabaseVersion]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBMProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDBXProps]
WHERE 1=0
UNION ALL
SELECT 1 As Col1 FROM [dbo].[RTblDTMProps]
WHERE 1=0
GO
The code is not very complex if there are more tables in the DB than you specify in the @UnionCount variable then it will do them in chunks of whatever you specified, if there are less then it will do all of them in 1 view.
Below is the code, if you have any questions then feel free to leave a comment.
USE msdbSET NOCOUNT ONDECLARE @UnionCount intSELECT @UnionCount = 20IF @UnionCount > 250 OR @UnionCount <1BEGINRAISERROR ('@UnionCount has to be between 1 and 250', 16, 1)RETURNENDSELECT identity(int,1,1) AS id,QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) AS tablenameINTO #TablesFROM information_schema.tablesWHERE table_type ='base table'AND OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped') = 0ORDER BY table_nameDECLARE @maxloop intDECLARE @loop intDECLARE @tablename varchar(200)SELECT @maxloop = MAX(id) FROM #TablesBEGINDECLARE @OuterLoopCount int, @OuterLoop intSELECT @OuterLoopCount = COUNT(*) FROM #TablesWHERE id %@UnionCount =0SELECT @OuterLoopCount = COALESCE(NULLIF(@OuterLoopCount,0),1)IF (SELECT COUNT(*) FROM #Tables) % 10 <> 0SELECT @OuterLoopCount = @OuterLoopCount +1SELECT @OuterLoop =1SELECT @Loop = MIN(id),@maxloop=MAX(id) FROM #Tables WHERE ID <= @UnionCount * @OuterLoopWHILE @OuterLoop <=@OuterLoopCountBEGINSELECT @Loop = MIN(id),@maxloop=MAX(id) FROM #Tables WHERE ID <= @UnionCount * @OuterLoopAND id > (@UnionCount * @OuterLoop) - @UnionCountPRINT'-- **************************** 'PRINT'-- **** View Starts Here ***** 'PRINT'-- **************************** 'PRINT 'CREATE VIEW DoNotDropMe_' + CONVERT(VARCHAR(10),@OuterLoop) + ' WITH SCHEMABINDING'+ char(10) + 'AS'WHILE @Loop <= @maxloop BEGINSELECT @tablename = tablename FROM #TablesWHERE id = @LoopPRINT 'SELECT 1 As Col1 FROM ' + @tablename + char(10) + 'WHERE 1=0' IF @Loop < @maxloop PRINT UNION ALL'SET @Loop = @Loop + 1ENDSET @OuterLoop = @OuterLoop + 1PRINT 'GO'PRINT ''PRINT ''ENDENDDROP table #TablesCross-posted from SQLBlog! -
http://www.sqlblog.com/