CODEWITHSUNDEEP
sqlsql-servert-sql
Tiawy
Tiawy

Reputation: 175

TSQL see if column is part of unique index without using dynamic management function

I want to write a query to see information about whether a column in a table is part of a unique index or not. Usually i write the following to get the information I need:

SELECT name
    , is_part_of_unique_key
      FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Department', NULL, 1)
     WHERE is_hidden = 0

the "is_part_of_unique_key" from the above query does the following according to MS:

Returns 1 if the column is part of a unique index (including unique and primary constraints) and 0 if it is not. Returns NULL if it cannot be determined that the column is part of a unique index. Is only populated if browsing information is requested.

Link: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql?view=sql-server-2017

however I am in a situation where I dont have SELECT-permissions on the above mentioned table, so the query above will not work.

I need the information about whether the column is part of a unique index or not by looking at i.e. the INFORMATION_SCHEMA-views

I have permissions to do the following for instance, but this doesn't give me information about unique indexes:

SELECT 
  COLUMN_NAME AS name
  ,DATA_TYPE AS system_type_name
  ,CHARACTER_MAXIMUM_LENGTH
  IS_NULLABLE 

FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE
            TABLE_NAME LIKE 'dbo.Department'

I am in doubt if sys.indexes will give me the correct results since i am interested in if the "column is part of a unique index (including unique and primary constraints)"

So I am only interested in unique indexes. How would I write a query to see if the column is part of a unique index with the restriced permissions i have?

Help is much appreciated!

Upvotes: 0

Views: 628

Answers (3)

kreadyf
kreadyf

Reputation: 510

Let's assume you have this table:

    CREATE TABLE MyIndexTestTable
    (
        MyIndexTestTableId INT NOT NULL IDENTITY(1,1),
        code VARCHAR(10) NOT NULL UNIQUE,
        otherUnique VARCHAR(10) NOT NULL,
        name VARCHAR(100) NULL,
        CONSTRAINT PK_MyIndexTestTable PRIMARY KEY(MyIndexTestTableId),
        CONSTRAINT UQ2_MyIndexTestTable UNIQUE (otherUnique)
    )

Then with the following statement you will get all columns with UNIQUE constraint and which are part of the primary key:

    SELECT COLUMN_NAME AS uniqueColumns FROM sys.objects c JOIN sys.objects t ON c.parent_object_id = t.object_id 
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu ON cu.TABLE_NAME = t.name AND cu.CONSTRAINT_NAME = c.name
    WHERE c.type IN ('PK', 'UQ')
    AND t.name = 'MyIndexTestTable'

This answer does not cover cases in which the primary key contains multiple columns - each of them are not unique. For this case you have to alter the query in order to exclude all primary key columns, if there is more than one. Please comment if you also need this example.

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272236

The following query will give you a list of unique indexes and columns involved with that index. Modify the where clause accordingly:

SELECT sys.indexes.name, sys.columns.name
FROM       sys.tables 
INNER JOIN sys.indexes       ON sys.tables.object_id        = sys.indexes.object_id
INNER JOIN sys.index_columns ON sys.indexes.object_id       = sys.index_columns.object_id
                            AND sys.indexes.index_id        = sys.index_columns.index_id
INNER JOIN sys.columns       ON sys.index_columns.object_id = sys.columns.object_id
                            AND sys.index_columns.column_id = sys.columns.column_id
WHERE sys.tables.name       = 'table name'
  AND sys.indexes.is_unique = 1

Upvotes: 2

Matt
Matt

Reputation: 98

Something like sp_help has the Column index_description. You can pull it from that Logic

sp_helptext sp_help

Grab it from this Section in the Procedure -- DISPLAY TABLE INDEXES & CONSTRAINTS
This may Work for you.

Sp_help My Example Table

Upvotes: 0

Related Questions