September 27, 2005 at 2:05 pm
Hello all,
I need to write a sql statement to retrieve the script of stored procedure/function/trigger, however, at least in SQL2000, if the obejct's text length > 4000, then it will be splitted to more than one records, I wonder how can I concatenate these records without using cursor or even temp table.
My script is as following:
select
so.name as [SPName], sc.colid as [ID], sc.name as [Parameter], st.name as [DataType], sc.length as [Size], sp.name as [Caption], sp.value as [ParameterEP], sm.text as [Script]
from sysobjects so
left outer join syscolumns sc on sc.id = so.id
left outer join systypes st on sc.xusertype = st.xusertype
left outer join syscomments sm on sm.id = so.id
left outer join sysproperties sp on (so.id = sp.id and sp.smallid = sc.colid and sp.type = 4)
where so.id = (select id from sysobjects where name ='dt_addtosourcecontrol')
This script actually has different result for different objects, for the in-code object dt_addtosourcecontrol, I got four records which is not correct, when applying to my own stored procedure with length > 4000, it returns correct result.
What's the problem? Do you have better solution? Thanks. I am expecting a solution that is not using Cursor or Temp table, ideally it's just a single SQL statement. This is limited by my application.
September 27, 2005 at 2:09 pm
Why is it unacceptable for the app to do some work in sorting this out?
September 27, 2005 at 2:13 pm
While I am sorting this out by doing something external work, I believe there should be a simple and better internal solution.
Thanks.
September 27, 2005 at 2:19 pm
I agree that there should be a better solution, but untill Yukon I'm afraid you'll be stuck with this solution because the [n][var]char columns are limited to 8K width. There's just no simple way to accomodate this problem server side ATM... assuming that Yukon will be able to solve it.
September 27, 2005 at 2:41 pm
Do it like sp_helptext does it.
It uses a cursor.
do a
use master
sp_helptext 'SP_HELPTEXT'
and it will show you how sp_helptext concatenates the two+ rows.
September 27, 2005 at 8:26 pm
I think you didn't notice my requirement: no cursor or temp table is allowed here.
Thanks.
September 28, 2005 at 9:51 am
And I think I'm indicating you cannot do it without using a cursor, temporary object or some other looping procedure.
you cannot inline query some rows to concatenate, and others not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply