Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

comma separated COLUMN_NAME

Posted on 2011-03-04
5
Medium Priority
?
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:Ephraim Wangoya
Ephraim Wangoya earned 1000 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 1000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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