• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Tables that contain a column all in one row in SQL 2000

When i run the code below,
"SELECT b.name AS ColumnName,a.name AS TableName FROM sysobjects AS a INNER JOIN syscolumns b ON a.id = b.id WHERE a.xtype = 'U' ORDER BY 1"
I get the column names and the tables they are in (if there are multiple tables that have the same column name, there are multiple rows for the columns).

I want to display something like this:
Column1                   TableA,TableC,TableT
Column5                   TableAA,TableFC,TableE

Raj's query below does it in SQL 2005. 'With' and 'XML' are new to 2005; I need a way to run this in 2000. how do you do it?

thanks

--2005
WITH CTETable AS
(
	SELECT b.name AS ColumnName,a.name AS TableName 
		FROM sysobjects AS a 
			INNER JOIN syscolumns b ON a.id = b.id 
	WHERE a.xtype = 'U' 
)
SELECT DISTINCT ColumnName, 
  NULLIF(   
    STUFF(   
      (SELECT ', ' + TableName FROM CTETable WHERE ColumnName = D.ColumnName FOR XML PATH('')  
                        ), 1, 2, ''   
    ), ''   
  ) AS AllTables 
FROM CTETable D
WHERE
        (SELECT Count(*) FROM CTETable WHERE ColumnName = D.ColumnName) > 1

Open in new window

0
anushahanna
Asked:
anushahanna
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need a function that concatenates the list per column name.


create function dbo.GetTablesOfColumn(@colname sysname)
returns nvarchar(4000)
as
begin
 declare @res nvarchar(4000)
 select @res = isnull(@res + ',' , '') + a.name
   FROM sysobjects AS a 
   JOIN syscolumns b ON a.id = b.id 
  WHERE a.xtype = 'U' 
    and b.name = @colname
 return @res
end

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and your query becomes:
select * 
  from (
select a.name, dbo.GetTablesOfColumn(a.Name) tables
  from syscolumns a
 ) sq
where sq.tables like '%,%'

Open in new window

0
 
anushahannaAuthor Commented:
Thanks angel.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now