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