THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

How To Protect Yourself From Fat-Finger Sally, Crazy Bosses and Other SQL Villains

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.

Of course I could have used sp_MSForEachtable but that is not documented  ;-(

 

 

USE msdb

SET NOCOUNT ON

 

DECLARE @UnionCount int

SELECT @UnionCount = 20

 

IF @UnionCount > 250 OR @UnionCount <1

BEGIN

RAISERROR ('@UnionCount has to be between 1 and 250', 16, 1)

RETURN

END

 

SELECT identity(int,1,1) AS id,QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) AS tablename

INTO #Tables

FROM information_schema.tables

WHERE table_type ='base table'

AND OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped') = 0

ORDER BY table_name

 

DECLARE @maxloop int

DECLARE @loop int

DECLARE @tablename varchar(200)

 

 

 

 

SELECT @maxloop = MAX(id)

FROM #Tables

 

BEGIN

      DECLARE @OuterLoopCount int, @OuterLoop int

 

      SELECT  @OuterLoopCount = COUNT(*) FROM #Tables

      WHERE id %@UnionCount =0

     

      SELECT @OuterLoopCount = COALESCE(NULLIF(@OuterLoopCount,0),1)

     

     

 

      IF (SELECT COUNT(*) FROM #Tables) % 10 <> 0

      SELECT @OuterLoopCount = @OuterLoopCount +1

     

      SELECT @OuterLoop =1

     

      SELECT @Loop = MIN(id),@maxloop=MAX(id)

      FROM  #Tables WHERE ID <= @UnionCount * @OuterLoop

     

      WHILE @OuterLoop <=@OuterLoopCount

      BEGIN

            SELECT @Loop = MIN(id),@maxloop=MAX(id)

            FROM  #Tables WHERE ID <= @UnionCount * @OuterLoop

            AND id > (@UnionCount * @OuterLoop)  - @UnionCount

     

           

            PRINT'--  **************************** '

            PRINT'--  ****  View Starts Here ***** '

            PRINT'--  **************************** '

            PRINT 'CREATE VIEW DoNotDropMe_' + CONVERT(VARCHAR(10),@OuterLoop) + ' WITH SCHEMABINDING'+ char(10) + 'AS'

            WHILE @Loop <= @maxloop

            BEGIN

                  SELECT @tablename = tablename

                  FROM #Tables

                  WHERE id = @Loop

                  PRINT 'SELECT 1 As Col1 FROM ' + @tablename  + char(10)  + 'WHERE 1=0'

                  IF  @Loop < @maxloop

                        PRINT  UNION ALL'

                  SET @Loop = @Loop + 1

                 

            END

      SET @OuterLoop = @OuterLoop + 1

      PRINT 'GO'

      PRINT ''

      PRINT ''

     

      END

END

 

 

DROP table #Tables

 

 

 

Published Wednesday, June 06, 2007 2:34 PM by Denis Gobo

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jeff said:

Tables like the YesNo table that you describe in the beginning can actually be very useful in SQL, for drop-down data sources, translating bits into words, cross joins, and other uses.  I've used them before and will continue to; even tables like "PlusMinus" where I had to cross join transactions with it to generate positive and negative values.

It's funny how small, insignificant tables can reduce your code by quite a bit and increase your flexibility by quite a bit as well, and how often most people assume "that table looks dumb! I'll just delete it!".

Anyway, I hope you learned your lesson -- and not just about deleting tables in general, but for assuming that small tables you don't understand are worthless without being familiar with the entire schema.  :)

June 15, 2007 2:06 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement