![[SQL]SPFINDSTRING](https://www.elitepvpers.com/forum/iconimages/sro-pserver-guides-releases/sql-spfindstring_ltr.gif){kind=small}
Aight.. it's a tiny cheesy script, not really sro-related, so feel free to move it if you find an appropriate section for it.
It's basically a way to find a given string (numerals work as well) in certain database objects which are here functions, procedures, triggers and tables.
I won't explain you in detail what it does and how it works, you should just run and play with it.
I'll give you an example anyhow..
31 = 1 (Function) + 2 (Procedure) + 4 (Trigger) + 8 (Table; String Columns) + 16 (Table; Numeric)
16 (Table; Numeric) is in the output excluded since 'char' isn't a numeric value.
Code:
CREATE PROCEDURE [dbo].[SPFINDSTRING] /*
by Caipi
1 » Function
2 » Procedure
4 » Trigger
8 » Table (String)
16 » Table (Numeric)
*/
@SEEK nvarchar(128),
@Types tinyint
as
SET NOCOUNT ON;
IF (LEN(@SEEK) = 0)
BEGIN
RAISERROR('Invalid SEEK length', 10, 1);
RETURN -1;
END
ELSE IF ((@Types & (1 | 2 | 4 | 8 | 16)) = 0)
BEGIN
RAISERROR('Invalid Types', 10, 1);
RETURN -2;
END
Declare @String nvarchar(max),
@obj_ID int,
@col_ID int,
@min_Hits int,
@obj_Type nvarchar(2),
@obj_Name nvarchar(128),
@col_Name nvarchar(128);
SELECT @String = N'',
@min_Hits = 0;
Declare @tbl_POOL TABLE(ObjectID int, ObjectName nvarchar(128), ObjectType nvarchar(2));
Declare @tbl_Results TABLE(ObjectID int, ObjectName nvarchar(128), ObjectType nvarchar(16), Keyword nvarchar(128), ColumnName nvarchar(128) NULL, min_Hits INT, Query nvarchar(max));
INSERT INTO @tbl_POOL
SELECT [object_id],
[name],
[type] FROM sys.objects WHERE [type] in ( CASE WHEN @Types & 1 != 0 THEN N'FN' ELSE N'' END,
CASE WHEN @Types & 2 != 0 THEN N'P' ELSE N'' END,
CASE WHEN @Types & 4 != 0 THEN N'TR' ELSE N'' END,
CASE WHEN @Types & 8 != 0 THEN N'U' ELSE N'' END,
CASE WHEN @Types & 16 != 0 THEN N'U' ELSE N'' END );
SELECT @obj_ID = MIN(ObjectID) FROM @tbl_POOL;
while (@obj_ID is not null)
BEGIN
SELECT @obj_Type = ObjectType,
@obj_Name = ObjectName FROM @tbl_POOL WHERE ObjectID = @obj_ID;
IF (@obj_Type in (N'FN', N'P', N'TR'))
BEGIN
SELECT @String = N'';
SELECT @String = @String + [text] FROM syscomments WHERE id = @obj_ID;
IF (CHARINDEX(@SEEK, @String) > 0)
BEGIN
INSERT INTO @tbl_Results SELECT @obj_ID, @obj_Name, CASE
WHEN @obj_Type = 'FN' THEN N'Function'
WHEN @obj_Type = 'P' THEN N'Procedure'
WHEN @obj_Type = 'TR' THEN N'Trigger'
END, @SEEK, N'', 1, N'';
END
END
ELSE IF (@obj_Type = N'U' AND @Types & 8 != 0)
BEGIN
SELECT @col_ID = MIN(column_id) FROM sys.columns WHERE [object_id] = @obj_ID AND collation_name is not null;
while (@col_ID is not null)
BEGIN
SELECT @col_Name = name FROM sys.columns WHERE column_id = @col_ID AND [object_id] = @obj_ID;
SELECT @String = N'';
SELECT @String = N'SELECT @Q = COUNT(1) FROM dbo.' + @obj_Name + N' with(NOLOCK) WHERE CHARINDEX(''' + @SEEK + N''', [' + @col_Name + N']) > 0';
exec sp_executesql @String, N'@Q INT OUTPUT', @min_Hits OUTPUT;
IF (@min_Hits > 0)
BEGIN
INSERT INTO @tbl_Results SELECT @obj_ID, @obj_Name, 'User_Table', @SEEK, @col_Name, @min_Hits, 'SELECT * FROM dbo.' + @obj_Name + ' with(NOLOCK) WHERE CHARINDEX(''' + @SEEK + N''', [' + @col_Name + N']) > 0';
END
SELECT @col_ID = MIN(column_id),
@col_Name = NULL,
@min_Hits = 0 FROM sys.columns WHERE [object_id] = @obj_ID AND collation_name is not null AND column_id > @col_ID;
END
END
ELSE IF (@obj_Type = N'U' AND @Types & 16 != 0 AND ISNUMERIC(@SEEK) = 1)
BEGIN
SELECT @col_ID = MIN(column_id) FROM sys.columns WHERE [object_id] = @obj_ID AND system_type_id in (127, 122, 108, 106, 62, 60, 59, 48, 52, 56);
while (@col_ID is not null)
BEGIN
SELECT @col_Name = name FROM sys.columns WHERE column_id = @col_ID AND [object_id] = @obj_ID;
SELECT @String = N'';
SELECT @String = N'SELECT @Q = COUNT(1) FROM dbo.' + @obj_Name + N' with(NOLOCK) WHERE [' + @col_Name + N'] = ' + @SEEK;
exec sp_executesql @String, N'@Q INT OUTPUT', @min_Hits OUTPUT;
IF (@min_Hits > 0)
BEGIN
INSERT INTO @tbl_Results SELECT @obj_ID, @obj_Name, 'User_Table', @SEEK, @col_Name, @min_Hits, 'SELECT * FROM dbo.' + @obj_Name + ' with(NOLOCK) WHERE [' + @col_Name + N'] = ' + @SEEK;
END
SELECT @col_ID = MIN(column_id),
@col_Name = NULL,
@min_Hits = 0 FROM sys.columns WHERE [object_id] = @obj_ID AND system_type_id in (127, 122, 108, 106, 62, 60, 59, 48, 52, 56) AND column_id > @col_ID;
END
END
SELECT @obj_ID = MIN(ObjectID) FROM @tbl_POOL WHERE ObjectID > @obj_ID;
END
SELECT * FROM @tbl_Results ORDER BY ObjectType, ObjectID;
SET NOCOUNT OFF;
RETURN 1;
It's written and tested on MSSQL 2008 R2; wrote it like a month ago or something. Have a nice one!
I attached the script with proper formattings ("[CODE]" screws it) below..
PS.: I welcome every public display of my mistakes and complications I made in my script.
. Great !
