Nasty SQL: Part 3
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.
Comments
Comment preview