May 15, 2011 at 11:31 pm
Hi everyone, I'm not sure if this requirement can be done in t-sql. The requirement is that i need to query all stored procudure that has varchar datatype declaration on any part of it's code. So the output should look like this.
CREATE Table #SPWithVarchar
(
SPName varchar(50), --Display the stored procedure name
LinePosint, --Display the line number to where the varchar was found.
TextPart nvarchar(255)) --Display the code to where the varchar was found
INSERT INTO #SPWithVarchar(SPName,LinePos,TextPart) VALUES ('Usp_Example1',36, '@Text varchar(10)')
INSERT INTO #SPWithVarchar(SPName,LinePos,TextPart) VALUES ('Usp_Example2',11, '@Desc varchar(100)')
INSERT INTO #SPWithVarchar(SPName,LinePos,TextPart) VALUES ('Usp_Example3',42, '@ItemDesc varchar(20)')
INSERT INTO #SPWithVarchar(SPName,LinePos,TextPart) VALUES ('Usp_Example4',29, '@GetText varchar(200)')
So far what i did is this:
SELECT SPECIFIC_CATALOG,
SPECIFIC_SCHEMA,
SPECIFIC_NAME,
DATA_TYPE
FROM
INFORMATION_SCHEMA.PARAMETERS AS ISP INNER JOIN
SYS.PROCEDURES AS SP
ON ISP.SPECIFIC_NAME = SP.[NAME]
WHERE
SPECIFIC_SCHEMA = 'myschema' AND
PARAMETER_MODE IN ('IN','INOUT') AND
DATA_TYPE = 'varchar'
GROUP BY SPECIFIC_CATALOG,
SPECIFIC_SCHEMA,
SPECIFIC_NAME,
DATA_TYPE
As you will notice I did not satisfied all the requirements using this query.
I hope someone could help.
Thanks,
May 16, 2011 at 8:04 am
The code you provided gives you all the parameters that are either passed in or out of the stored procedure, so they will all be at the beginning of the proc. Are you also looking for local variables that are defined as varchar? I'm not sure why you would need to know what lines they are declared on within the proc. Have you looking in information_Scheme.routines?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2011 at 8:14 am
I think he needs to find all the varchar variables and change them to nvarchar.
In that I'd do a search in the proc's definition for varchar. You could also do a smarter search that finds the position of all the words varchar that don't have a n 1 position earlier.
Then the only false positives are going to be converts and the likes.
You'd have manual work to do but you'd have a darn good start.
May 16, 2011 at 9:05 am
Something like this?
SELECT REPLACE(c.name, '@', '') AS parameter, t.name AS typename,
Object_name(c.object_id) AS stored_procedure
FROM sys.parameters c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id AND parameter_id > 0
WHERE t.name = 'varchar'
May 16, 2011 at 10:51 pm
Ninja's_RGR'us (5/16/2011)
I think he needs to find all the varchar variables and change them to nvarchar.
Thanks to all that reply to this post.
Yes you are correct. I need to change them to nvarchar that's why I also need to determine the line number so i'ts not that hard to change it manually.
May 17, 2011 at 5:01 am
InfiniteError (5/16/2011)
Ninja's_RGR'us (5/16/2011)
I think he needs to find all the varchar variables and change them to nvarchar.Thanks to all that reply to this post.
Yes you are correct. I need to change them to nvarchar that's why I also need to determine the line number so i'ts not that hard to change it manually.
Ah. I understand now. Only way I can think of would be to use a cursor: -
CREATE TABLE #SPWithVarchar
(SPName VARCHAR(MAX), --Display the stored procedure name
LinePos INT, --Display the line number to where the varchar was found.
TextPart VARCHAR(MAX)) --Display the code to where the varchar was found
--First temporary table, required to get the names of the stored-procedures
CREATE TABLE #TestTemp (Code VARCHAR(MAX), sp_name VARCHAR(MAX))
INSERT INTO #TestTemp(Code, sp_name)
--sp_helptext "hides" an ugly while loop, so a set-based version would be
--preferred. I can't think of a way of getting the line numbers without it
--unfortunately.
SELECT 'EXEC sp_helptext ''' + o.name + '''', '''' + o.name + ''''
FROM sysobjects o
WHERE o.xtype = 'P' AND o.category = 0
GROUP BY o.name
--Next, build up our cursor. Which I have poetically named "crap", showing
--my general feeling on them :)
DECLARE @code VARCHAR(MAX), @sp_name VARCHAR(MAX)
DECLARE crap CURSOR FOR
SELECT code, sp_name FROM #TestTemp
OPEN crap
FETCH NEXT FROM crap INTO @code, @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
--Second temporary table, holds the results of "sp_helptext" for each individual
--stored-procedure. Uses an identity column to determine the line number of
--the stored-procedure.
--We then search this table before inserting into your #SPWithVarchar table.
CREATE TABLE #TestTemp2 (Number INT IDENTITY,Line VARCHAR(MAX), sp_name VARCHAR(MAX))
SET @code = 'INSERT INTO #TestTemp2(Line) ' + @code
--Insert results of "sp_help" into second temp table
EXECUTE ('' + @code + '')
--Updates second temporary table with the stored-procedure name.
UPDATE #TestTemp2 SET sp_name = @sp_name
--Inserts the lines from #TestTemp2 where "varchar" appears into #SPWithVarchar.
INSERT INTO #SPWithVarchar(spname, linepos, TextPart)
SELECT sp_name, number, line FROM #TestTemp2 WHERE line LIKE '%varchar%'
--Drop #TestTemp2 so it's ready to be used for the next stored-procedure.
DROP TABLE #TestTemp2
FETCH NEXT FROM crap INTO @code, @sp_name
END
--Clean up
CLOSE crap
DEALLOCATE crap
DROP TABLE #TestTemp
--Display the results
SELECT * FROM #SPWithVarchar
--DROP TABLE #SPWithVarchar
Pretty sure that gives you what you want - but it may take awhile to execute! 😉
--Edit--
Commented code a bit.
May 17, 2011 at 7:16 am
Running the following code will give you the names and the code of all stored procedures with a varchar declaration. It will not give you the line number, but if you want to do a search and replace, then perhaps line number is not that crucial. I join to the syscomments table twice in order to return the complete code set for each stored procedure, not only those records that actually have the "varchar" text in them.
selectdistinct
p.name as 'Stored Procedure Name',
c_display.colid as 'Code Sequence',
c_display.[text] as 'Stored Procedure Code'
fromsys.procedures p
inner joinsys.syscomments c_search
on c_search.id = p.[object_id]
and (c_search.[text] like '%varchar(%' or c_search.[text] like '%varchar (%')
inner joinsys.syscomments c_display
on c_display.id = p.[object_id]
order byp.name, c_display.colid, c_display.[text]
Hakim Ali
www.sqlzen.com
May 17, 2011 at 3:47 pm
I use this for character string searches. It will bring back 1 row for each sp. Then open the SP to search for all the uses. You can easily tweak this for your needs.
I like sql_modules (sql2005) better than syscomments (sql2000) because it does not have the problem of character strings spanning multiple syscomments lines, which would not be found without some convoluted code:
----------------- NEW, uses sys.sql_modules
-- Find the string 'xxxxxxxxxx' in Stored procedures, then select 150 characters that contain it
-- the -25 returns results that start before the character string so you can see if it's part of a comment or code.
select name, crdate, CHARINDEX('varchar', definition),substring(definition,CHARINDEX('varchar', definition)-25,150) as 'Code Snippet'--, definition as 'Entire Code'
from sys.sql_modules com
join sysobjects obj on com.object_id = obj.id
where definition like '%varchar%'
and definition not like '%nvarchar%'
order by name
May 17, 2011 at 5:51 pm
That won't work correctly. You can have 2 variables declared 1 as nvarchar and 1 as varchar.
To do this you need to save the spot where you find the varchar string and make sure that the previous character is not a n (or research for nvarchar and make sure the starting position is different).
May 17, 2011 at 10:31 pm
hakimali (5/17/2011)
Running the following code will give you the names and the code of all stored procedures with a varchar declaration. It will not give you the line number, but if you want to do a search and replace, then perhaps line number is not that crucial. I join to the syscomments table twice in order to return the complete code set for each stored procedure, not only those records that actually have the "varchar" text in them.
selectdistinct
p.name as 'Stored Procedure Name',
c_display.colid as 'Code Sequence',
c_display.[text] as 'Stored Procedure Code'
fromsys.procedures p
inner joinsys.syscomments c_search
on c_search.id = p.[object_id]
and (c_search.[text] like '%varchar(%' or c_search.[text] like '%varchar (%')
inner joinsys.syscomments c_display
on c_display.id = p.[object_id]
order byp.name, c_display.colid, c_display.[text]
I think I can work with this, Thanks to everyone that reply to this post.
May 18, 2011 at 10:07 am
Ninja's_RGR'us (5/17/2011)
That won't work correctly. You can have 2 variables declared 1 as nvarchar and 1 as varchar.To do this you need to save the spot where you find the varchar string and make sure that the previous character is not a n (or research for nvarchar and make sure the starting position is different).
It's actually much simpler than that. You just have to modify the pattern for your LIKE operator.
WHERE definition LIKE '%[^n]varchar%'
What this says is that you're looking for any 'varchar' where the immediately preceding character is anything but 'n' (ruling out 'nvarchar').
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 18, 2011 at 10:30 am
So much simpler!!!!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply