Nasty SQL: Part 3

time to read 8 min | 1469 words

I realized that I didn't really explain why anyone would ever need this nastiness, the idea is piping dynamic SQL inside the database. I realize that this doesn't really mean anything, so here is the example. First, we need to expand our nasty trigger to support multiply statements:

CREATE TABLE Nasty( [text] nvarchar(500));

GO

CREATE TRIGGER Very_Nasty_Trigger

ON NASTY

INSTEAD OF INSERT

AS BEGIN

    DECLARE @stmt nvarchar(500);

      DECLARE statements CURSOR

      FOR select [text] from INSERTED;

      OPEN statements

      FETCH NEXT FROM statements

            INTO @stmt

      WHILE @@FETCH_STATUS != -1

      BEGIN

            exec sp_executesql @stmt

            FETCH NEXT FROM statements

                  INTO @stmt

      END

      CLOSE statements

      DEALLOCATE statements

END

Now we can use it to find out stuff about our database using the InformationSchema tables, like so:

INSERT INTO Nasty([text])

SELECT 'SELECT COUNT(*), '''+table_name+''' FROM '+table_name FROM information_schema.tables

This is quick and dirty way to find out how much rows there are in each of your tables. Ten points to the first person who can recognize where similar technqiue is widely used.