SQL Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query Help

Author

Topic

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2012-12-07 : 00:14:57

Hi Guys

I'm stuck in writting a sql query. Please help me out.

I have a tabble which records the dependencies of each object

For Example

TableName ObjectType Dependent OnTable
---------------------------------------------------------
Table1 Table NULL Table1
Table2 Table NULL Table1
Table3 Table NULL Table1

Sp1 Storedproc Yes Table1
Sp1 Storedproc Yes Table2
Sp1 Storedproc Yes Table3
Sp1 Storedproc Yes View1
Sp1 Storedproc Yes View2

I want to write a query to find dependent of each Object. I need something recursive. Please help

Thanks in advance

Select Knowledge from LearningProcess

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-07 : 00:43:17

Can you provide expected result?

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-07 : 01:45:09

[code] This one?

DECLARE @dep TABLE(TableName varchar(10), ObjectType varchar(30), [Dependent] varchar(10), OnTable varchar(10))
INSERT INTO @dep
SELECT 'Table1', 'Table', NULL, 'Table1' union all
SELECT 'Table2', 'Table', NULL, 'Table1' union all
SELECT 'Table3', 'Table', NULL, 'Table1' union all
SELECT 'Sp1', 'Storedproc', 'Yes', 'Table1' union all
SELECT 'Sp1', 'Storedproc', 'Yes', 'Table2' union all
SELECT 'Sp1', 'Storedproc', 'Yes', 'Table3' union all
SELECT 'Sp1', 'Storedproc', 'Yes', 'View1' union all
SELECT 'Sp1', 'Storedproc', 'Yes', 'View2'

SELECT tablename, STUFF((SELECT ',' + OnTable FROM @dep d2 WHERE d1.TableName = d2.TableName FOR XML PATH('')),1,1, '') AS depObjects
FROM @dep d1
WHERE Tablename != OnTable
GROUP BY tablename

OUTPUT:
tablename depObjects
Sp1 Table1,Table2,Table3,View1,View2
Table2 Table1
Table3 Table1[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-07 : 07:59:28

see

http://visakhm.blogspot.in/2010/01/finding-cross-server-cross-db-object.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources