September 14, 2015 at 4:12 pm
Hi
I was wondering if anyone knows a better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my dev\test\prod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows. I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query. Anyone run into this issue before or have any suggestions?
Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')
from (
SELECT o.[name], o.[type], C.text--,*
FROM syscomments C JOIN [sys].[objects] o
ON C.id = o.object_id
WHERE colID = 1) col1
left outer join (
SELECT o.[name], o.[type], C.text--,*
FROM syscomments C JOIN [sys].[objects] o
ON C.id = o.object_id
WHERE colID = 2) col2 on col1.name = col2.name and col1.[type] = col2.[type]
left outer join (
SELECT o.[name], o.[type], C.text--,*
FROM syscomments C JOIN [sys].[objects] o
ON C.id = o.object_id
WHERE colID = 3) col3 on col1.name = col3.name and col1.[type] = col3.[type]
left outer join (
SELECT o.[name], o.[type], C.text--,*
FROM syscomments C JOIN [sys].[objects] o
ON C.id = o.object_id
WHERE colID = 4) col4 on col1.name = col4.name and col1.[type] = col4.[type]
left outer join (
SELECT o.[name], o.[type], C.text--,*
FROM syscomments C JOIN [sys].[objects] o
ON C.id = o.object_id
WHERE colID = 5) col5 on col1.name = col5.name and col1.[type] = col5.[type]
left outer join (
SELECT o.[name], o.[type], C.text--,*
FROM syscomments C JOIN [sys].[objects] o
ON C.id = o.object_id
WHERE colID = 6) col6 on col1.name = col6.name and col1.[type] = col6.[type]
left outer join (
SELECT o.[name], o.[type], C.text--,*
FROM syscomments C JOIN [sys].[objects] o
ON C.id = o.object_id
WHERE colID = 7) col7 on col1.name = col7.name and col1.[type] = col7.[type]
where col1.[name] = 'My_Proc'
Thanks!
September 14, 2015 at 4:17 pm
Try using the new system views since it looks like you are using SQL Server 2012. Check out this one: sys.sql_modules.
Please note that you may not see the entire procedure in the results pane of SSMS.
September 14, 2015 at 4:31 pm
Hi Lynn
Well that certainly worked a lot better! Thank you so much! It returns the code all on one row.
Regards,
Jeff
September 15, 2015 at 4:48 pm
FYI, you can also use the function:
OBJECT_DEFINITION()
to get that text.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply