December 29, 2010 at 7:27 am
Hi Folks,
Anyone know a way of querying an SP to retrieve the internal declarations and create statements? Looking to review all sp's and don't particularly want to use syscomments/sp_HelpText to retrieve text and then parse all the sql to pick these out - so many ways of declaring something!
Thanks
Jamie
December 29, 2010 at 10:08 am
What are you ultimately trying to do? If you are looking to find dependencies that can be difficult.
sys.sql_modules is a SQL 2005/2008 update from the 2000 syscomments table. It contains all of the DDL in one column so you don't have to worry about object names spanning rows in syscomments.
-- search DDL of db objects for specific terms
SELECT
o.Type_Desc
,SCHEMA_NAME(o.SCHEMA_ID) AS Schema_Name
,SCHEMA_NAME(o.SCHEMA_ID) + '.' + o.NAME AS Obj_Name
FROM sys.objects o
INNER JOIN sys.sql_modules c
ON o.object_id = c.object_id
WHERE
c.definition LIKE '%%' -- text you are searching for goes here
ORDER BY
o.type_desc
,SCHEMA_NAME(o.SCHEMA_ID)
,o.NAME
-- show stored proc parameters
SELECT
p.SPECIFIC_CATALOG AS DatabaseName
,p.SPECIFIC_SCHEMA AS SchemaName
,p.SPECIFIC_NAME AS ProcName
,p.ORDINAL_POSITION AS OrdPos
,p.PARAMETER_MODE AS Mode
,p.PARAMETER_NAME
,p.DATA_TYPE
,p.CHARACTER_MAXIMUM_LENGTH
,p.CHARACTER_OCTET_LENGTH
FROM
INFORMATION_SCHEMA.PARAMETERS p
INNER JOIN sys.objects o
ON p.SPECIFIC_NAME = o.NAME
AND o.TYPE = 'p'
AND o.is_ms_shipped = 0
ORDER BY
p.SPECIFIC_NAME
,p.ORDINAL_POSITION
December 29, 2010 at 3:43 pm
Hi Eric,
Thanks for the reply. I can see that it would list the SP as per EXEC sp_HelpText and parameters.
What I'm trying to achieve is a lookup of all declarations in an SP/all SP's. E.g.
CREATE PROC dbo.Sample (
@Aardvark INT,
@Aardman VARCHAR(12) NULL)
AS
DECLARE @Sausage BIT, @Stuffing Numeric(28,9)
CREATE TABLE #TmpTable (Vegetable INT KEY, Description VARCHAR(30))
--Lots of enlightening squirrels chopped up here
GO
This would return all the parameters
@Aardvark INT,
@Aardman VARCHAR(12) NULL)
All the declarations
@Sausage BIT
@Stuffing Numeric(28,9)
All the create tables/table valued declarations
Vegetable INT
Description VARCHAR(30))
For all SPs in order to check that @Aardvark is an int and not a numeric(28,9) in the db. That @Sausage is a bit and not a TINYINT etc.
Ultimate aim is a list - I can get all the tables and parameters (neat example BTW), its the "declares" and "creates" that confuddle the nogin. Don't particularly want to do a text parse as I'm sure I'll miss a rule and miss tables etc. because I've taken the extra bracket from a varchar and used that to stop processing a table instead....
Phew sorry for the long winded explanation - just sure I'm missing something!
January 4, 2011 at 4:24 pm
Hi Folks,
Just to let you know, resorted to parsing the SQL in 4th gen language.
Touch wood - so far its not too bad...
Kind Regards
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply