December 12, 2008 at 4:22 pm
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
December 12, 2008 at 4:36 pm
I'm sorry, but I don't see any errors in your post. What errors are you receiving?
December 12, 2008 at 5:55 pm
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