[SQL] Some of my favorite INFORMATION_SCHEMA utility queries

Phil just posted about using INFORMATION_SCHEMA to bulletproof your SQL change scripts. I've been working up a post on using queries against INFORMATION_SCHEMA views to generate SQL scripts, so this seems like a good time to chime in. The INFORMATION_SCHEMA views are views which describe a database's objects and schema. Phil did a great job of explaining what the INFORMATION_SCHEMA views are, so go take a look at his post if you'd like to know more. As the title of his post indicates, he's using INFORMATION_SCHEMA to make SQL change scripts more robust. I'm going to focus on using ad-hoc queries against INFORMATION_SCHEMA to save time.

The INFORMATION_SCHEMA views allow you to use SQL queries against your database schema, a fact you can exploit by writing queries which generate SQL. I usually shift to text output mode (Ctrl-T in both QA and SSMS), execute the ad-hoc query, copy the output to a new query window, and run it. You can consolidate this to one step; I'll discuss that in a bit.   

---------------------------------------------------------------------------------------------------
--Generate delete statements for all tables in the current database.
--Note that foreign key constraints will throw errors the first time you run this,
--but given the fact that you're deleting from every table in the database you don't care.
--Unless you've got circular FK constraints, you can just keep hitting execute (F5) 
--until the errors go away...
---------------------------------------------------------------------------------------------------
select 'DELETE FROM ' + quotename(table_name) + ';' from information_schema.tables where table_type = 'BASE TABLE'

---------------------------------------------------------------------------------------------------
--Usually when I delete all data, I want to reset autoincrement identity fields.
--This query will generate scripts to do that for you.
---------------------------------------------------------------------------------------------------
select 'DBCC CHECKIDENT ('''+quotename(table_name)+''', RESEED, 0);' from information_schema.tables where table_type = 'BASE TABLE'

---------------------------------------------------------------------------------------------------
--This generates scripts to drop every table in the database. I usually use this to generate
--all the scripts, then modify it to keep static lookup table data (state names, member types, etc.).
---------------------------------------------------------------------------------------------------
select 'DROP TABLE ' + quotename(table_name) + ';' from information_schema.tables where table_type = 'BASE TABLE'

---------------------------------------------------------------------------------------------------
--This generates the change scripts to change ownership to dbo for all objects owned by someone else.
---------------------------------------------------------------------------------------------------
select 
'EXEC(''sp_changeobjectowner @objname = '''''+ 
  (table_schema) + '.' + (table_name) + '''''' 
  + ', @newowner = dbo'')'
from information_schema.tables
where table_schema!='dbo'
order by table_schema,table_name

select 
'EXEC(''sp_changeobjectowner @objname = '''''+ 
  (routine_schema) + '.' + (routine_name) + '''''' 
  + ', @newowner = dbo'')'
from information_schema.routines
where routine_schema!='dbo'
order by routine_schema,routine_name

---------------------------------------------------------------------------------------------------
--And if you just want a listing of tables and views that aren't owned by dbo:
---------------------------------------------------------------------------------------------------
select 
table_name
from information_schema.tables
where table_schema!='dbo'
order by table_schema,table_name

Oops - that last one didn't generate any SQL, it just ran a report. Well, sure, it's slick to use SQL to generate SQL, but sometimes you just want information.  This came in handy the other day - Phil found a SQL Injection vulnerability in some code we'd inherited, and it turned out that a stored procedure was at fault. The ASP.NET code was doing things correctly - using ADO.NET parameters, etc. - but the query was concatenating them into a SQL string and executing it. Whoops! I wanted get an idea of how many more procs were following that pattern, and this query did the trick:

---------------------------------------------------------------------------------------------------
--This makes a quick and dirty list of stored procedures which may execute dynamic sql.
---------------------------------------------------------------------------------------------------
select 
'/' + replicate('*',10) + routine_name + replicate('*',10) + '/', 
routine_definition 
from information_schema.routines 
where routine_definition like '%exec %'
or routine_definition like '%execute %'
or routine_definition like '%sp_executesql %'

As I said before, my preference is to generate scripts and execute them in two distinct steps. This allows you to keep the scripts in version control, but more importantly it allows you to review the script before you run it. That's especially important when you're running scripts which could potentially affect every object in your database. However, if you want to merge these two steps, you can use a technique I previously wrote about which executes a dynamic query once for each row in a temporary table. Notice in the query below that I've just inserted one of the above scripts between the "--Begin statement" and "--End statement" comments:

---------------------------------------------------------------------------------------------------
--Create and execute the SQL in one step...
---------------------------------------------------------------------------------------------------
USE [DBNAME]
GO

declare @RowCnt int
declare @MaxRows int
declare @ExecSql nvarchar(255)

select @RowCnt = 1

declare @statements table (rownum int IDENTITY (1, 1) Primary key NOT NULL , statement varchar(255))
insert into @statements (statement)
--Begin statement
select 
'EXEC(''sp_changeobjectowner @objname = '''''+ 
  ltrim(table_schema) + '.' + ltrim(table_name) + '''''' 
  + ', @newowner = dbo'')'
from information_schema.tables
where table_schema!='dbo'
order by table_schema,table_name
--End statement

select @MaxRows=count(1) from @statements

while @RowCnt <= @MaxRows
begin
    select @ExecSql = statement from @statements where rownum = @RowCnt 
    print @ExecSql
    execute sp_executesql @ExecSql
    Select @RowCnt = @RowCnt + 1
end

Once you start using the INFORMATION_SCHEMA views, you'll find more and more uses for them. Some people go off the deep end and use these things to generate ASP.NET code; I think that's just going a bit too far. If you've got some other favorite uses of the INFORMATION_SCHEMA views, please leave them in the comments below.

And of course, these views are only one of may ways to get meta with your data - there's tons of fun to be had with SMO, and some day I might get around to posting about using Excel to generate SQL, XML, and HTML...

3 Comments

  • Why not use truncate table XXX

    For deleting and resetting the auto increment column in one swoop ??

  • Very cool. Honestly, I've been using T-SQL for years now and Phil's post was the first time I had heard of using the INFORMATION_SCHEMA view. You really do learn something new every day. :)

  • @Mischa - I usually like to do the delete / reseed in two steps. One reason is that some of the deletes may fail the first time due to FK constraints, and there's no point reseeding the other tables over and over. I run the delete scripts until all tables are empty, then reseed them all at once. Either way would probably work, but this seems simpler to me.

Comments have been disabled for this content.