March 17, 2010 at 11:09 am
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
March 17, 2010 at 11:13 am
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
March 17, 2010 at 11:22 am
Thanks but I need to change the behavior of the stored procedure I mentioned.
Alessandro
March 17, 2010 at 11:28 am
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
March 17, 2010 at 11:39 am
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
end
March 18, 2010 at 4:56 am
alx.beneventi (3/17/2010)
Hello to everyone. I'd like to change the stored procedure sys.sp_columns to make this changeselect @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
June 21, 2010 at 3:08 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply