Month: June 2008

SQL – Using COALESCE to Build Comma-Delimited String

I had a query that returned one column with multiple records.  I wanted only one record with all the values to be comma separated.  I could do this with a cursor, but that throws a big warning sign.  So i decided to use the COALESCE function.  Below are some of the resources i used to solve my problem.
 
 

–**************************************************************************
–http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

DECLARE

@EmployeeList varchar(100)

SELECT

@EmployeeList = COALESCE(@EmployeeList + ‘, ‘, ”) +

CAST(Emp_UniqueID AS varchar(5))

FROM

SalesCallsEmployees

WHERE

SalCal_UniqueID = 1

SELECT

@EmployeeList

–**************************************************************************
–http://www.sqlservercentral.com/scripts/Miscellaneous/31922/

create

procedure sp_return_students

as
set

nocount off

/* Declare variable which will store all student name */

Declare

@StudentName varchar(8000)

/* Query that will return student names and at the same time concatenate values. */

select

@StudentName = coalesce(@StudentName + ‘, ‘, ”) + stu_name from tbl_students

/* At last, you just have to define column name that will store values */

Select

@StudentName As Student

How to find SQL Relationship and Dependencies (Dependency)

 
 
SQL SERVER – Query to display Foreign Key relationships and name of the constraint for each table in Database.
I tested the query at the following website and it seems to work.  In the response section someone claims to have modified the query and made it faster.  A where can be added to it easily to limit it to a specific table.
 
This is a cool system Stored Procedrue, but I believe it’s not documented.
EXEC sp_MSdependencies N’mo_company’, null, 1315327
 
System Stored Procedure that is documented, but doesn’t give you what you really want. But kinda cool.
EXEC sp_depends @objname = N’tablename’
 
*****************************************************************************************
EXEC sp_depends @objname = N’tablename’
 
EXEC sp_MSdependencies N’tablename’, null, 1315327
*****************************************************************************************
SQL SERVER – Query to display Foreign Key relationships and name of the constraint for each table in Database.
This is very long query. Optionally, we can limit the query to return results for one or more than one table.

SELECT
     K_Table  = FK.TABLE_NAME,
     FK_Column = CU.COLUMN_NAME,
     PK_Table  = PK.TABLE_NAME,
     PK_Column = PT.COLUMN_NAME,
     Constraint_Name = C.CONSTRAINT_NAME
FROM       INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN      INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN  (
     SELECT      i1.TABLE_NAME, i2.COLUMN_NAME
     FROM        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
         INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
             WHERE       i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
     ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
—- optional:
ORDER BY
     1,2,3,4
WHERE      PK.TABLE_NAME=’something’WHERE      FK.TABLE_NAME=’something’

WHERE      PK.TABLE_NAME IN (‘one_thing’, ‘another’)

WHERE      FK.TABLE_NAME IN (‘one_thing’, ‘another’)

*****************************************************************************************

SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME