November 19, 2008 at 10:05 am
I'm trying to get some results to show me all the tables & views used in all the Stored Procedures in a database. I can easily search syscomments for 1 string such as "TableA"
[font="Courier New"]select id, text from syscomments where text like '%table_A%'[/font],
But how do I find & display multiple strings in the same column ?
For instance, syscomments may look like this:
id text
-- --------------------------------------------------------------------
27 select * from Table_A A join Table_B B on A.key = B.key
And I want the results to show:
id tables
-- ----------
27 Table_A
27 Table_B
I'm pretty sure I've seen this type of thing answered before, but couldn't find the old posts.
November 19, 2008 at 10:21 am
Here is some code.
But this will run like a dog if it's run on all tables/comments
DECLARE @tbltables TABLE
([Name] VARCHAR(10))
INSERT INTO @tbltables
SELECT 'Table_A' UNION
SELECT 'Table_B'
DECLARE @Comments TABLE
(Id INT,
[text] VARCHAR(MAX))
INSERT INTO @Comments
SELECT 27,'select * from Table_A A join Table_B B on A.key = B.key'
SELECT
c.Id,
t.[Name]
FROM @Comments c
INNER JOIN @tbltables t
ON c.[text] LIKE '%' + t.[Name] + '%'
Can't you use sysdepends???
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 19, 2008 at 10:24 am
this is the first time I've used this table bu maybe something like this:
SELECT DISTINCT
p.Name,
c.Name
FROM sysdepends d
INNER JOIN sys.Objects p ON p.Object_ID = d.Id
INNER JOIN sys.Objects c ON c.Object_ID = d.depId
ORDER BY p.Name
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 20, 2008 at 2:30 am
Did this work?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 20, 2008 at 7:32 am
Not quite, but maybe I can tweak it a bit,
November 20, 2008 at 7:59 am
cool,
Could you post your solution once you find the correct answer or if you need more help let us know.
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 20, 2008 at 9:02 am
I took a bit of a different approach to examine the SPs. I used your idea of sysdepends by using sp_depends in a loop of all the stored procs to give me a list of all tables used in the SPs:
set nocount on
declare @sp-2 varchar(128)
--set @sp-2 = 'sp_name_here'
--select @sp-2, 'sp_depends ''' + @sp-2 + ''''
declare @cmd varchar(500)
-- Table for all the SPs
create table #StoredProc ( Table_Name varchar(128), type varchar(50), updated varchar(5),
selected varchar(5), column_name varchar(50))
create table #StoredProc_2 ( Table_Name varchar(128), type varchar(50), updated varchar(5),
selected varchar(5), column_name varchar(50), SP_Name varchar(128))
declare SPs cursor for
select name from sysobjects where xtype = 'P' and name like 'usp_%'
--information_schema.tables where table_type = 'base table'
open SPs
fetch next from SPs into @sp-2
while @@fetch_status = 0
begin
set @cmd = 'sp_depends ''' + @sp-2 + ''''
insert into #StoredProc exec (@cmd)
insert into #StoredProc_2 (Table_Name,type, updated, selected, column_name, SP_Name)
select Table_Name,type, updated, selected, column_name, @sp-2 from #StoredProc
fetch next from SPs into @sp-2
end
CLOSE SPs
DEALLOCATE SPs
select Table_Name, column_name, SP_Name, updated, selected,type from #StoredProc_2 order by table_name, column_name,sp_name
select Table_Name, column_name, SP_Name, updated, selected,type from #StoredProc_2 order by table_name, sp_name
select Table_Name, column_name, SP_Name, updated, selected,type from #StoredProc_2 order by sp_name, table_name
--order by tablename
drop table #StoredProc
drop table #StoredProc_2
The only risk with using dependencies is that every once in a while someone might create or update a stored proc before a table is created and then the info does not get into sysdepnds. But I think this will give me what I need for now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply