THE SQL Server Blog Spot on the Web

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

Louis Davidson

There is still stuff I hadn't noticed (OBJECT_ID has a parameter for type of object)

I was building a script to build a table I had built in my dev environment including inserts so I did a bit of hunting around and came upon Vyas's script ( that is used to generate inserts from a table (script here:  Very cool stuff and worked nicely (all I did was specify a table.)

What was really interesting was that his drop or create batch to start the script looked like this:

IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists   
          PRINT 'Procedure already exists. So, dropping it'
          DROP PROC sp_generate_inserts

Hmmm: OBJECT_ID('sp_generate_inserts','P'))?  What is P?  Well, looking at BOL it is the type of object!  Cool. 

After creating the stored procedure, running the following commands:

select OBJECT_ID('sp_generate_inserts')
select OBJECT_ID('sp_generate_inserts','P')
select OBJECT_ID('sp_generate_inserts','U')

Will tell you 1, that there is an object named sp_generate_inserts, it is a procedure, and it is not a table.  Very useful.  Of course, because a procedure has to be the only thing created in a batch, this information is not tremendously useful in building better scripts.  But it is interesting new feature.

Now, what is really interesting new thing is addition of database_id as a parameter to object_name.  But I will talk about that later...

Crossposted to

Published Sunday, December 3, 2006 4:42 PM by drsql

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


No Comments

Leave a Comment


This Blog


Links to my other sites


Privacy Statement