immanuel2112 (10/7/2015)

---

Hi,

I tried to using the suggestion we have in this thread. But when executing it i am getting the 2 errors,

1. Msg 156, Level 15, State 1, Line 4 - Incorrect syntax near the keyword 'VIEW'.

2. Msg 102, Level 15, State 1, Line 21 - Incorrect syntax near 'END'.

Below is the query, Please advise.

declare @query nvarchar(max);

SET @query='USE <DatabaseName>;

IF NOT EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''PrimaryKeyDetails'')

BEGIN

CREATE VIEW PrimaryKeyDetails AS

BEGIN

SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)), CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)),

PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)), PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE, 

REFERENCE_TABLE_NAME='', REFERENCE_COL_NAME=''

FROM sys.key_constraints as PKnUKEY

INNER JOIN sys.tables as PKnUTable

ON PKnUTable.object_id = PKnUKEY.parent_object_id

INNER JOIN sys.index_columns as PKnUColIdx

ON PKnUColIdx.object_id = PKnUTable.object_id

AND PKnUColIdx.index_id = PKnUKEY.unique_index_id

INNER JOIN sys.columns as PKnUKEYCol

ON PKnUKEYCol.object_id = PKnUTable.object_id

AND PKnUKEYCol.column_id = PKnUColIdx.column_id

INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl

ON oParentColDtl.TABLE_NAME=PKnUTable.name

AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name

END

';

print @query;

EXECUTE sp_executesql @query;

Cheers,

Immi