Situatie
Mai jos este un script care listeaza toti indexii de pe o baza de date MSSQL.
Solutie
SELECT DB_NAME() AS [dbname],
[so].[name] AS ‘tablename’,
[si].[indid],
[si].[rowcnt],
[si].[name] AS [indname],
[sik].[keyno] AS [colorder],
[sc].[name] AS [colname],
CASE
WHEN [si].[status]&2048 <> 0
THEN ‘yes’
ELSE ‘no’
END AS ‘IsPrimaryKey’,
CASE
WHEN([si].[status]&2 <> 0)
OR ([si].[status]&4096 <> 0)
THEN ‘yes’
ELSE ‘no’
END AS ‘IsUnique’,
CASE
WHEN OBJECTPROPERTY([so].[id], ‘IsMSShipped’) = 1
THEN ‘yes’
ELSE ‘no’
END AS ‘IsMSShipped’
FROM [sysindexes] AS [si]
JOIN [sysobjects] AS [so] ON [si].[id] = [so].[id]
JOIN [sysindexkeys] AS [sik] ON [si].[id] = [sik].[id]
AND [si].[indid] = [sik].[indid]
JOIN [syscolumns] AS [sc] ON [sik].[id] = [sc].[id]
AND [sik].[colid] = [sc].[colid]
WHERE [si].[status]&64 = 0– and so.name = ‘” & tbl & “‘
AND OBJECTPROPERTY([so].[id], ‘IsMSShipped’) = 0
ORDER BY 1,2
Leave A Comment?