comma separated COLUMN_NAME

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

LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Ephraim WangoyaCommented:
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
 
Ephraim WangoyaCommented:
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
 
MuffyBunnyCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
thank you both for looking.  i've got enough to put something together.
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.

All Courses

From novice to tech pro — start learning today.