[p]Oh dear. This article really should have explained more. As it stands, I'm afraid it is rather misleading. Although it is right in saying that many such procedures will work in some ways as system stored procedures just by putting them in the Master database and by prefixing them with 'sp_', there are differences (which vary from version to version). One important one is that until the sp has been registered as a system stored procedure, it won't be able to see the Object Catalog views for the database, only the master ones. The only reason that the example works is that it uses the old system tables, which for compatibility reasons can be seen. Unfortunately, anyone who takes the advice in this article will become very frustrated. Only if a stored procedure is flagged as being MS_Shipped will it behave in every way as the author suggests. The code in the article will work, but it will not if you use the Object Catalog Views. This won't work. ...[/p]

create PROCEDURE sp_FindTableNames

(

@ColumnName varchar(128)

) AS

SELECT *

FROM sys.objects

WHERE object_id IN

(

SELECT object_id

FROM sys.columns

WHERE name = @ColumnName

)

AND type = 'U'

go

[p]You can get around this by registering the stored procedure but there is no way of undoing it. You have to delete and recreate if you need to alter it. Also, this stored procedure is officially 'undocumented' though well known, so you use it at your peril and (all the usual caveats).[/p]

if not exists (SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'sp_FindTableNames' and IS_MS_SHIPPED=1)

EXEC sp_ms_marksystemobject 'sp_FindTableNames'

[p]My advice is that it is best not to create a system stored procedure unless you fully understand the repercussions, and have the necessary permi9ssions. I agree it is very magical to have your own special versions of SP_Who, and SP_Help, but please use caution. It is probably best to have a copy of your stored procedure in the database that you are developing, though I agree that this entails a lot more housekeeping and ls likely to leave unwanted scaffolding on your nice new database, if you don't tidy up.[/p]