我需要获取特定数据库中所有唯一键限制和索引的列表。我的做法如下:
SELECT * FROM sys.sysobjects WHERE type!='u' AND name LIKE <tablename>
我想确认这是否是正确的方式,还是有更好的方法做同样的事情?
我需要获取特定数据库中所有唯一键限制和索引的列表。我的做法如下:
SELECT * FROM sys.sysobjects WHERE type!='u' AND name LIKE <tablename>
我想确认这是否是正确的方式,还是有更好的方法做同样的事情?
由于唯一约束条件在底层是作为索引实现的,因此您可以直接从sys.indexes获取所有这些信息:
SELECT
[schema] = OBJECT_SCHEMA_NAME([object_id]),
[table] = OBJECT_NAME([object_id]),
[index] = name,
is_unique_constraint,
is_unique,
is_primary_key
FROM sys.indexes
-- WHERE [object_id] = OBJECT_ID('dbo.tablename');
为了在所有数据库上重复执行(并且可能没有针对特定表的过滤器):DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'SELECT db = ' + name + ',
[schema] = OBJECT_SCHEMA_NAME([object_id]),
[table] = OBJECT_NAME([object_id]),
[index] = name,
is_unique_constraint,
is_unique,
is_primary_key
FROM ' + QUOTENAME(name) + '.sys.indexes;'
FROM sys.databases
WHERE database_id BETWEEN 4 AND 32766;
EXEC sp_executesql @sql;
其他答案没有为我返回完整的列表。这个查询对我有效,可以返回所有不是主键或系统表的唯一索引:
最初的回答
select i.name as index_name, o.name as object_name
from sys.indexes i
join sys.objects o on i.object_id= o.object_id
where (i.is_unique_constraint = 1 OR i.is_unique = 1)
and i.is_primary_key = 0 and o.type_desc <> 'SYSTEM_TABLE'
--====================================================================================
-- Display SQL Server Index Details – Type, Key Columns, Included Columns
-- https://www.ptr.co.uk/blog/sql-server-display-indexes-their-columns-included-columns
--====================================================================================
--Display all indexes along with key columns, included columns and index type
DECLARE @TempTable AS TABLE (SchemaName VARCHAR(100),
ObjectID INT,
TableName VARCHAR(100),
IndexID INT,
IndexName VARCHAR(100),
ColumnID INT,
column_index_id INT,
ColumnNames VARCHAR(500),
IncludeColumns VARCHAR(500),
NumberOfColumns INT,
IndexType VARCHAR(20),
IS_UNIQUE BIT,
Is_Primary_Key BIT,
Is_Unique_Constraint BIT,
LastColRecord INT);
WITH CTE_Indexes (SchemaName, ObjectID, TableName, IndexID, IndexName,
ColumnID, column_index_id, ColumnNames, IncludeColumns,
NumberOfColumns, IndexType, Is_Unique,Is_Primary_Key,Is_Unique_Constraint)
AS
(
SELECT s.name, t.object_id, t.name, i.index_id, i.name, c.column_id, ic.index_column_id,
CASE ic.is_included_column WHEN 0 THEN CAST(c.name AS VARCHAR(5000)) ELSE '' END,
CASE ic.is_included_column WHEN 1 THEN CAST(c.name AS VARCHAR(5000)) ELSE '' END,
1, i.type_desc,I.is_unique,i.Is_Primary_Key,i.Is_Unique_Constraint
FROM sys.schemas AS s
JOIN sys.tables AS t ON s.schema_id = t.schema_id
JOIN sys.indexes AS i ON i.object_id = t.object_id
JOIN sys.index_columns AS ic
ON ic.index_id = i.index_id
AND ic.object_id = i.object_id
JOIN sys.columns AS c
ON c.column_id = ic.column_id
AND c.object_id = ic.object_id
AND ic.index_column_id = 1
UNION ALL
SELECT s.name, t.object_id, t.name, i.index_id, i.name, c.column_id, ic.index_column_id,
CASE ic.is_included_column WHEN 0 THEN CAST(cte.ColumnNames + ', ' + c.name AS VARCHAR(5000))
ELSE cte.ColumnNames END,
CASE
WHEN ic.is_included_column = 1 AND cte.IncludeColumns != ''
THEN CAST(cte.IncludeColumns + ', ' + c.name AS VARCHAR(5000))
WHEN ic.is_included_column =1 AND cte.IncludeColumns = ''
THEN CAST(c.name AS VARCHAR(5000))
ELSE ''
END,
cte.NumberOfColumns + 1, i.type_desc,I.is_unique,I.Is_Primary_Key,i.Is_Unique_Constraint
FROM sys.schemas AS s
JOIN sys.tables AS t ON s.schema_id = t.schema_id
JOIN sys.indexes AS i ON i.object_id = t.object_id
JOIN sys.index_columns AS ic
ON ic.index_id = i.index_id
AND ic.object_id = i.object_id
JOIN sys.columns AS c
ON c.column_id = ic.column_id
AND c.object_id = ic.object_id
JOIN CTE_Indexes cte
ON cte.Column_index_ID + 1 = ic.index_column_id
--JOIN CTE_Indexes cte ON cte.ColumnID + 1 = ic.index_column_id
AND cte.IndexID = i.index_id AND cte.ObjectID = ic.object_id
)
INSERT INTO @TempTable
SELECT *,
RANK() OVER (PARTITION BY ObjectID, IndexID ORDER BY NumberOfColumns DESC) AS LastRecord
FROM CTE_Indexes AS cte;
SELECT SchemaName, TableName, IndexName, ColumnNames, IncludeColumns, IndexType, Is_Unique,Is_Primary_Key,Is_Unique_Constraint
FROM @TempTable
WHERE LastColRecord = 1
--WHERE T.object_id=OBJECT_ID('dbo.invoiceSelectedOptionalFees')
--AND objectid=OBJECT_ID('dbo.accountTransaction')
AND objectid=OBJECT_ID('dbo.invoiceSelectedOptionalFees')
ORDER BY objectid, TableName, indexid, IndexName
IF OBJECT_ID('[dbo].[invoiceSelectedOptionalFees]') IS NOT NULL
DROP TABLE [dbo].[invoiceSelectedOptionalFees]
GO
CREATE TABLE [dbo].[invoiceSelectedOptionalFees] (
[Id] INT IDENTITY(1,1) NOT NULL,
[invoiceId] INT NOT NULL,
[feeTypeId] INT NOT NULL,
CONSTRAINT [PK__invoiceS__3214EC0703C8693D] PRIMARY KEY CLUSTERED ([Id] asc),
CONSTRAINT [CK_invoiceSelectedOptionalFees_invoice_feeType] UNIQUE NONCLUSTERED ([invoiceId] asc, [feeTypeId] asc),
CONSTRAINT [FK_invoiceSelectedOptionalFees_invoice] FOREIGN KEY ([invoiceId]) REFERENCES [invoice]([invoiceID]),
CONSTRAINT [FK_invoiceSelectedOptionalFees_feeTypes] FOREIGN KEY ([feeTypeId]) REFERENCES [feeTypes]([feeTypeID]))
唯一约束在 sys.objects 中以类型 'UQ' 表示。
select name from sys.objects where type='UQ'
select i.name, o.name from sys.indexes i
inner join sys.objects o on i.object_id= o.object_id
您可以从sys.indexes获取唯一键约束和索引。 具体来说,唯一约束:
select * from sys.indexes where is_unique_constraint = 1