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.
Author |
Topic |
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2014-07-08 : 03:21:29
|
Hi,with the below query iam able to retrieve all the tables invloved in a stored proc.but,what i want to display the table names as comma separated list for each table.;WITH stored_procedures AS (SELECT o.id,o.name AS proc_name, oo.name AS table_name,ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS rowFROM sysdepends d INNER JOIN sysobjects o ON o.id=d.idINNER JOIN sysobjects oo ON oo.id=d.depidWHERE o.xtype = 'P')SELECT id,proc_name, table_name FROM stored_proceduresWHERE row = 1ORDER BY proc_name,table_nameThanks,javeedJaveed Ahmed |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-07-08 : 09:44:15
|
;WITH stored_procedures AS (SELECT o.id, o.name AS proc_name, oo.name AS table_name,ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS rowFROM sysdepends d INNER JOIN sysobjects o ON o.id=d.idINNER JOIN sysobjects oo ON oo.id=d.depidWHERE o.xtype = 'P')SELECT DISTINCT proc_name, STUFF((SELECT ','+table_name FROM stored_procedures WHERE id= p.id FOR XML PATH('')), 1, 1, '') TableNamesFROM stored_procedures pWHERE row = 1--Chandu |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2014-07-10 : 04:59:49
|
Hi Chandu,Did you notice,the table names are repeating in the output?Javeed Ahmed |
|
|
Ranjay Kumar
Starting Member
3 Posts |
Posted - 2014-07-10 : 05:57:10
|
Hiiii Javeed AhmedUse below code after stuff,You will never get Duplicate tables.SELECT distinct ','+table_name FROM stored_procedures WHERE id= p.id FOR XML PATH('')), 1, 1, ''Ranjay Kumarranjay |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-07-10 : 05:59:20
|
[code]SELECT distinct id, proc_name, table_name = stuff(tb.table_names, 1, 1, '')FROM stored_procedures sp cross apply ( select ',' + table_name from stored_procedures x where x.id = sp.id group by table_name for xml path('') ) tb (table_names)WHERE row = 1ORDER BY proc_name, table_name[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
Ranjay Kumar
Starting Member
3 Posts |
Posted - 2014-07-10 : 06:00:46
|
[quote]Originally posted by Ranjay Kumar Hiiii Javeed AhmedUse below code after stuff in Mr Chandu Query ,You will never get Duplicate tables.SELECT distinct ','+table_name FROM stored_procedures WHERE id= p.id FOR XML PATH('')), 1, 1, ''Ranjay Kumarranjay |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2014-07-10 : 06:09:58
|
great thanksJaveed Ahmed |
|
|
|
|
|
|
|