Identify columns which will create a unique record in a table

Question

Post reply

Identify columns which will create a unique record in a table

mcinvalek

SSC-Addicted

Points: 429
More actions
September 15, 2014 at 12:37 pm

#296298

I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back. Any advise is greatly appreciated

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 1

ALTER TABLE <YourTableName> ADD ID int NOT NULL IDENTITY (1, 1)

ALTER TABLE <YourTableName> ADD CONSTRAINT PK_<YourTableName> PRIMARY KEY (ID)

Alex Suprun

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Could you use the Data Profiling Task in SSIS?

http://msdn.microsoft.com/en-us/library/bb895263(v=sql.105).aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Alexander Suprun (9/15/2014)
ALTER TABLE <YourTableName> ADD ID int NOT NULL IDENTITY (1, 1)
ALTER TABLE <YourTableName> ADD CONSTRAINT PK_<YourTableName> PRIMARY KEY (ID)

Alex,

That seems like an easy patch instead of doing a good analysis. That could generate more problems than it can solve.

mcinvalek,

Taking an idea from Alex's comment, you could try to see if someone did their job correctly by generating PKs or UNIQUE constraints.

WITH cteTally AS(

SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5

)

SELECT OBJECT_NAME(o.parent_object_id) AS [table_name]

,o.NAME AS [constraint_name]

,CASE

WHEN o.type = 'PK'

THEN 'PRIMARY KEY'

ELSE 'UNIQUE'

END + CASE

WHEN index_id = 1

THEN ' (clustered)'

ELSE ' (non-clustered)'

END AS [TYPE]

,STUFF((

SELECT ISNULL(', ' + index_col(OBJECT_NAME(o.parent_object_id), i.index_id, n), '')

FROM cteTally

FOR XML PATH('')

), 1, 2, '') AS [Columns]

FROM sys.objects o

JOIN sys.indexes i ON o.parent_object_id = i.object_id

AND o.NAME = i.NAME

WHERE o.type in ('PK','UQ')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mcinvalek SSC-Addicted Points: 429 More actions · Answer 4

I am so sorry for having wasted anyone's time. I have found exactly what I am looking for. I had searched before adding my post but did not discover this post until this am.

http://www.sqlservercentral.com/scripts/T-SQL/62086/

Mr McLain's piece is perfect.