Alter sys.sp_columns in the current database

Question

Alter sys.sp_columns in the current database

alx.beneventi

SSC Enthusiast

Points: 146
More actions
March 17, 2010 at 11:09 am

#156789

Hello to everyone. I'd like to change the stored procedure sys.sp_columns to make this change
select @fUsePattern = 1
to
select @fUsePattern = 0
Can anyone tell me if it is possible and, if yes, how?
Best regards
Alessandro

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I don't think you can modify the system procs, but you can open it up, change the name, and create your own custom proc that'll do what you need. Will that solve whatever problem you're trying to deal with?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

alx.beneventi SSC Enthusiast Points: 146 More actions · Answer 2

Thanks but I need to change the behavior of the stored procedure I mentioned.

Alessandro

GSquared SSC Guru Points: 260824 More actions · Answer 3

You'll need to talk to Microsoft about that. I'm pretty sure it's not possible. I could be wrong, but I seriously doubt it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Not sure why you want to modify the behaviour of a system stored proc, but looking at it, this code snippet seems to indicate if there are no wild cards in the any of the input arguments, the value is swithced to 0 and equality operators are used instead of using the LIKE operator.

if (@fUsePattern = 1) -- Does the user want it?

begin

if ((isnull(charindex('%', @full_table_name),0) = 0) and

(isnull(charindex('_', @full_table_name),0) = 0) and

(isnull(charindex('[', @table_name),0) = 0) and

(isnull(charindex('[', @table_owner),0) = 0) and

(isnull(charindex('%', @column_name),0) = 0) and

(isnull(charindex('_', @column_name),0) = 0) and

(@table_id <> 0))

begin

select @fUsePattern = 0 -- not a single wild char, so go the fast way.

end

Paul White SSC Guru Points: 150467 More actions · Answer 5

alx.beneventi (3/17/2010)
Hello to everyone. I'd like to change the stored procedure sys.sp_columns to make this change
select @fUsePattern = 1 to select @fUsePattern = 0
Can anyone tell me if it is possible and, if yes, how?

Copy the procedure body, modify it, and save it as a user-defined procedure.

Changing a system stored procedure is possible, but daft in the extreme.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

neven_mahovic Valued Member Points: 51 More actions · Answer 6

Hello everyone!

Has anyone succeed in changing sp_columns procedure or doing something about extremely poor performance in certain cases.

There is a problem (on SQL 2008 but I believe the same is with 2005) if there is a _ (or % or ....) in table name and user is not a member of sysadmin role.

sp_procedure is performing more than 80 times slower compared to table with no _ or compared to performance when user is a member of sysadmin group.

I realy don't care if solution is a hack, because this problem seems to be rather old and MS don't care to solve it.

There are some solutions proposing using sp_columns90 but in my case sp_columns is called from VB6 application with ODBC connection and there is no way to influence sp_columns call.

Now, with new, extremely faster hardware I am facing slower response than with SQL 2000 just because of poor sp_columns performance.

I am even wondering if MS is doing this on purpose for some funny reasons.

Regards,

Neven