September 27, 2005 at 1:55 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:08 pm
Please do not cross-post, we monitor all boards.
Finish the thread here : http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=224123
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply