Solved

comma separated COLUMN_NAME

Posted on 2011-03-04
5
314 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

829 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