Useful Commands To Search Columns or Stored Procedures

Tags

stored procedure, TSQL

***************************************************
* search for a column name
***************************************************
SELECT sysobjects.name as “Table”,
syscolumns.name as “Column”
from sysobjects , syscolumns
where sysobjects.id = syscolumns.id
and sysobjects.xtype = ‘u’
and syscolumns.name like ‘%id%’
order by sysobjects.name, syscolumns.name

****************************************************

***************************************************
* search for a stored procedure
***************************************************
SELECT B.name AS ‘Schema’, A.name AS ‘Procedure Name’
FROM sys.sysobjects A join sys.schemas B
ON B.schema_id = A.uid
WHERE xtype=’P’ AND A.name LIKE ‘%Products%’
****************************************************

***************************************************
* find a stored procedure containing
***************************************************
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%foobar%’
AND ROUTINE_TYPE=’PROCEDURE’
**************************************************

TSQL Function: Convert Varchar To Number

Tags

TSQL

Below is the function created by Damian Edwards to strips out non-numeric characters, after doing that I can convert to numeric safely (after checking for empty strings and nulls of course):

— Function to strip out non-numeric chars
ALTER FUNCTION dbo.UDF_ParseNumericChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
–SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @string)
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9.]%’, @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
SET @IncorrectCharLoc = PATINDEX(‘%[^0-9.]%’, @string)
END
SET @string = @string
RETURN @string
END
GO