Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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

0
dbaSQL
Asked:
dbaSQL
  • 2
  • 2
2 Solutions
 
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
 
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

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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