[sql]spfindstring

Felt like releasing something again ;d

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 desired 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..

http://img16.imageshack.us/img16/853...bc7b979245.png

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.

ThX MaN

This is very useful when you have to search all tables that have same ID for example.
Thx

good job

great just yesterday i thought about something like this and then did it manually instead xD

Nice job :wink: