THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 (http://vyaskn.tripod.com/) that is used to generate inserts from a table (script here: http://vyaskn.tripod.com/code/generate_inserts_2005.txt).  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   
   BEGIN
          PRINT 'Procedure already exists. So, dropping it'
          DROP PROC sp_generate_inserts
   END
GO

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 http://drsql.spaces.live.com

Published Sunday, December 03, 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

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

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