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

drsql — Sun, 03 Dec 2006 21:42:00 GMT

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

Easier access to metadata coming soon to a SQL Server near you

drsql — Mon, 25 Sep 2006 23:35:00 GMT

This is not a scoop or anything, but it is quite newsworthy. It is also a good way to kick off my sqlblog er...blog:

Kalen Delaney - Metadata Power
Simon Sabin - object_name() to be extended in SP2

A very common request is to have some of the system functions to work in any database. Kalen posted a piece of feedback on connect to allow the object_name function to work with an optional second parameter of database_id! Wow! Thank you thank you! I hope this becomes a standard feature on all of the database specific functions...

And for those of you who (like me) have started to embrace schemas, they also added a function: object_schema_name that returns the schema_name of the object.

I had also asked for something like the latter, but they just closed it and told me to do this:
SCHEMA_NAME(OBJECTPROPERTY(<object_id>, 'schemaid')). Well, I would listen to Kalen over me too :)

Crossposted on drsql.spaces.live.com

Louis Davidson : System Functions

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

Easier access to metadata coming soon to a SQL Server near you