To find string content inside stored procedure, you can use this query:
Declare @StringToSearch varchar(100)
SET @StringToSearch = '%something%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
--AND SO.Type <> 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
Different types in sysobjects:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
source: here
To find the column names from tables, you may use this query
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%colname%'
ORDER BY schema_name, table_name;
or use this query:
SELECT Table_Schema, Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE table_name in ( select name from sysobjects
where xtype = 'U' )
and column_name like '%colname%'
order by table_schema, table_name
No comments:
Post a Comment