Solved

comma separated COLUMN_NAME

Posted on 2011-03-04
5
312 Views
Last Modified: 2012-05-11
Having a heck of a time returning my index and constraint keys in a comma separated list.  I don't want to go in by @tablename, I just want to run the select against INFORMATION_SCHEMA, and return output like this:

TableName, IndexName, IndexKey,ConstraintName,ConstraintKey

Where IndexName, IndexKey and ConstraintName, ConstraintKey may be null in some cases (where indexes or constraints don't exist), and where they do exist, I want the key columns returned in one line, separated by commas, in either IndexKey or ConstraintKey.

Instead of this:
FK_tblAddress_tblApplicant      AddressId,
FK_tblAddress_tblApplicant      ApplicantId,

I'd like to return this:
FK_tblAddress_tblApplicant      AddressId,ApplicantId

My initial select is below, any advice would be much appreciated.
SELECT DISTINCT 
	c.TABLE_NAME TableName,
	i.[name] IndexName,
	c.CONSTRAINT_NAME ConstraintName,
	k.COLUMN_NAME ColumnName
FROM SYS.INDEXES I
INNER JOIN SYS.ALL_OBJECTS O
  ON I.[OBJECT_ID] = O.[OBJECT_ID]
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
  ON O.NAME = C.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
  ON C.TABLE_NAME = K.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('sysdiagrams','dtproperties')  
ORDER BY C.TABLE_NAME

Open in new window

0
Comment
Question by:dbaSQL
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35039997
try
SELECT ISNULL(c.TABLE_NAME + ',', '') +
	            ISNULL(i.[name] + ',', '') +
	            ISNULL(c.CONSTRAINT_NAME + ',', '') +
	            ISNULL(k.COLUMN_NAME + ',', '')
FROM SYS.INDEXES I
INNER JOIN SYS.ALL_OBJECTS O
  ON I.[OBJECT_ID] = O.[OBJECT_ID]
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
  ON O.NAME = C.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
  ON C.TABLE_NAME = K.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('sysdiagrams','dtproperties')  
ORDER BY C.TABLE_NAME

Open in new window

0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 250 total points
ID: 35040011
or change it to
SELECT c.TABLE_NAME +
	   ISNULL(',' + i.[name], '') +
	   ISNULL(',' + c.CONSTRAINT_NAME, '') +
	   ISNULL(',' + k.COLUMN_NAME, '')
FROM SYS.INDEXES I
INNER JOIN SYS.ALL_OBJECTS O
  ON I.[OBJECT_ID] = O.[OBJECT_ID]
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
  ON O.NAME = C.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
  ON C.TABLE_NAME = K.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('sysdiagrams','dtproperties')  
ORDER BY C.TABLE_NAME

Open in new window

0
 
LVL 6

Accepted Solution

by:
MuffyBunny earned 250 total points
ID: 35040322
Here's a scaled down version. You'll have to do some tweaking to get the rest of your columns in there, but this should put you on the right path.

SELECT T.Name as TABLE_NAME,
	(SELECT C.Name + ',' 
	FROM SYSCOLUMNS AS C 
	WHERE C.id = T.id 
	ORDER BY C.Name 
	FOR XML PATH('')) AS COLUMN_NAMES  
FROM SYSOBJECTS AS T 
WHERE T.xtype = 'U'

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35041107
yeah, this is the same problem i was having earlier.  the tblAddress table has on PK on AddressID and one FK on ApplicantID.  Initially, I believe this is what I'd like to see:

tblAddress, null, null, PK_tblAddress, AddressID
tblAddress, null, null, FK_tblAddress_tblApplicant, ApplicantID

But now I think it would be better to include the FK's in the same single result-line as well, like this:

TableName, IndexName, IndexKey,PKName,PKColumn,FKName,FKColumn
tblAddress,null,null, PK_tblAddress, AddressID, FK_tblAddress_tblApplicant

where there is no index or pk or fk, the values would just be null


This is what I return with your suggestions, ewangoya:
tblAddress,PK_tblAddress,PK_tblAddress,ApplicantId
tblAddress,PK_tblAddress,PK_tblAddress,AddressId
tblAddress,PK_tblAddress,FK_tblAddress_tblApplicant,ApplicantId
tblAddress,PK_tblAddress,FK_tblAddress_tblApplicant,AddressId

I've also been kicking the for xml construct around, too, MuffyBunny.  still just having a hard time getting the values together, beyond the example you've posted.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 35041557
thank you both for looking.  i've got enough to put something together.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now