Stored Procedure

  • When I run this stored procedure I just want it to return the data in the columns table. But it returns all these invalid object errors.

    set nocount on

    go

    set Ansi_Warnings on

    go

    set ANSI_NULLS on

    go

    alter proc spp_FindText @text varchar(128)

    as

    IF EXISTS (SELECT 1

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE='BASE TABLE'

    AND TABLE_NAME='servers')

    drop table servers

    IF EXISTS (SELECT 1

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE='BASE TABLE'

    AND TABLE_NAME='databases')

    drop table databases

    IF EXISTS (SELECT 1

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE='BASE TABLE'

    AND TABLE_NAME='columns')

    drop table columns

    CREATE TABLE servers(sname VARCHAR(255))

    CREATE TABLE DATABASES(SERVERNAME VARCHAR(255), NAME VARCHAR(128))

    CREATE TABLE columns(FoundIn varchar(100),servername varchar(100), databasename varchar(100),tableName VARCHAR(255),columnname varchar(245), LineNumber int,Procname varchar(200))

    INSERT servers EXEC master..XP_CMDShell 'OSQL -L'

    Insert into servers select @@servername

    update servers set sname = ltrim(sname)

    DELETE servers WHERE sname='Servers:' or sname like '%(local)%' or sname is null OR (SNAME NOT LIKE 'CL%' and SNAME not like 'SV%')

    create index idx_ServName on servers (sname)

    Declare search cursor for

    Select sname FROM servers

    DECLARE @Servname varchar(200)

    DECLARE @TableName varchar(200)

    DECLARE @Type char(1)

    Declare @ColumnName varchar(128)

    --DECLARE @LINKSERVER VARCHAR(128)

    DECLARE @sql VARCHAR(1000)

    DECLARE @SQL1 VARCHAR(1000)

    DECLARE @SQL2 VARCHAR(1000)

    DECLARE @SQL3 VARCHAR(1000)

    --set @text = 'dst'

    --SET @LINKSERVER = 'EXEC SP_ADDLINKEDSERVER "' + @SERVNAME + '"'

    Open Search

    Fetch Next From Search into @Servname

    While @@Fetch_Status = 0

    Begin

    SET @sql = 'INSERT INTO DATABASES select ' + char(39) + @servname + char(39) + ' AS SERVERNAME, name from [' + @servname + ']. master.dbo.sysdatabases'

    EXEC(@SQL)

    delete from databases where name in ('model','master','tempdb','msdb')

    Declare databases cursor for

    Select name FROM databases

    declare @database varchar(100)

    Open databases

    Fetch Next From databases into @database

    While @@Fetch_Status = 0

    Begin

    set @sql1 = ('insert into columns (foundin,servername,databasename,tablename, columnname)SELECT ''Column'' as Foundin,' + char(39) + @servname + char(39) + ' as Servername, ' + char(39) + @database + char(39) + ' as databasename,Table_name AS TableName, Column_name as Columnname FROM '+ @database + '.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ''%' + @text + '%''')

    exec(@sql1)

    set @sql2 = ('insert into columns (foundin,servername,databasename,tablename) SELECT ''Table'' as Foundin,' + char(39) + @servname + char(39) + ' as Servername, ' + char(39) + @database + char(39) + ' as databasename,Table_name AS TableName FROM '+ @database + '.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ''%' + @text + '%''')

    exec (@sql2)

    insert into columns (foundin, servername, databasename,linenumber,procname)SELECT DISTINCT 'Proc' as Foundin,'' + @servname + '' as Servername, '' + @database + '' as databasename, Len(SubString(text,1, PatIndex('%' + @text + '%', text)))-Len(Replace(SubString(text,1, PatIndex('%' + @text + '%', text)),char(13),''))+1 AS Line,

    OBJECT_NAME(id) AS ProcName

    FROM syscomments

    WHERE text like '%' + @text + '%'

    ORDER BY ProcName, Line

    Fetch Next From databases into @database

    end

    close databases

    deallocate databases

    Fetch Next From Search into @Servname

    end

    close Search

    deallocate search

    go

    select * from columns order by 1,2,3,4

    go

  • I'm sorry, but I don't see any errors in your post. What errors are you receiving?

  • If the stored procedure doesn't already exist then you will need to change the alter to a create at the top of the script.

    Also, the select statement at the end of the script will fail until the first time the stored procedure has been called. The first part of the stored procedure creates the table called columns

    Hope that helps,

    David

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply