Category Archives: Schema

Find all unused indexes in a database

Indexes, Schema, Scripts September 3, 2013 Leave a comment

While doing an index analysis activity i had to find the indexes are hardly being used in our production DB’s. I wrote this script to acheive the same

SELECT case when os.is_published = 1 then 'Replicated' else 'Not Replicated'
end as ReplicatedorNot,
OBJECT_NAME(idx.object_id) AS TableName ,
idx.name as Index_name,
istat.user_lookups,
istat.user_scans,
istat.user_seeks,
istat.user_updates
FROM sys.dm_db_index_usage_stats AS istat
JOIN sys.indexes AS idx ON idx.index_id = istat.index_id
join sys.objects os
on idx.object_id = os.object_id
AND idx.object_id = istat.object_id
WHERE
istat.database_id = DB_ID('databasename') -- type the name of the Db
and object_name(idx.object_id) not like 'Ms%'
and object_name(idx.object_id) not like 'sys%'
and object_name(idx.object_id) not like 'id_%'
and object_name(idx.object_id) not like 'conflict%'
and object_name(idx.object_id) not like 'Any other tables that you like to avoid%'
AND idx.is_unique_constraint = 0 -- no unique indexes
AND idx.is_primary_key = 0
AND idx.is_disabled = 0
AND idx.type > 1 -- don't consider heaps/clustered index
AND ( ( istat.user_seeks + istat.user_scans +istat.user_lookups )  <  istat.user_updates
or (istat.user_seeks = 0 AND istat.user_scans = 0 AND istat.user_lookups = 0))
order by (istat.user_seeks + istat.user_scans +
istat.user_lookups)
Advertisement

Primary Keys which are not Clustered Indexes in a database

Schema, Scripts September 2, 2013 Leave a comment

Recently i was asked to find all the Primary keys that are not clustered Indexes in the database.I constructed the below script which will help us to locate the primary keys in the database which are not clustered index.

Use DatabaseName
go
SELECT idx.name AS IndexName
, fg.name AS Filegroup
, object_name(idx.object_id) AS TableName
, CASE
WHEN index_id = 1 THEN
‘Clustered’
WHEN index_id = 0 THEN
‘heap’
ELSE
‘Non Clustered’
END AS TypeOfIndex
, CASE
WHEN idx.is_primary_key = 1 AND index_id > 1 THEN
‘Primary key as Unique Non Clustered Index’
WHEN idx.is_primary_key = 1 AND index_id = 1 THEN
‘PK and Clustered’
ELSE
‘Unique Non-Cl’
END AS PrimaryKeyAsNonCLIndex
FROM
sys.filegroups fg
JOIN sys.indexes idx
ON fg.data_space_id = idx.data_space_id
JOIN sys.objects O
ON object_name(idx.object_id) = O.name
WHERE
object_name(idx.object_id) NOT LIKE ‘sys%’
AND idx.is_primary_key = 1
AND index_id > 1
ORDER BY
TableName

Find Increment Values and Current Identity value of all tables in a database

Schema, Scripts September 1, 2013 Leave a comment

Use DatabaseName
Go
SELECT IDENT_SEED(TABLE_NAME) AS SeedValue,
IDENT_INCR(TABLE_NAME) AS IncrementValue,
IDENT_CURRENT(TABLE_NAME) AS Current_Identity_Value,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘TableHasIdentity’) = 1
AND TABLE_TYPE = ‘BASE TABLE’

What is the difference between Unique Index and Unique Constraint

Schema September 1, 2013 Leave a comment

There is no difference between a unique constraint and a unique index other than the fact that the unique constraint is listed as a constraint in the database. A unique constraint cannot be disabled.The status of a constraint does not allow the unique constraint any additional functionality over a unique index. Even though the syntax for both are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table.

Let us start by building and example:
USE AdventureWorks
go
CREATE TABLE dbo.Test
( ID int NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test] ADD CONSTRAINT [CK_Test] UNIQUE
(
[ID] ASC
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IDX_Test_ID] ON [dbo].[Test]
(
[ID] ASC
)
GO
exec sp_helpconstraint Test
exec sp_helpindex Test

Snapshot of the output can been seen below.

Unique Index

Find all the Foriegn Key’s and their details in a database

Schema, Scripts September 1, 2013 Leave a comment

The below script helps us to find all the foreign keys in a database and there details. i have used AdventureWorks2012 db as example.

USE AdventureWorks2012
go
SELECT fk.name AS ForeignKey,
IsNotForReplication = case when fk.is_not_for_replication = 0 then ‘No’ else ‘yes’
end,
OBJECT_NAME(fk.parent_object_id) AS TableName,
COL_NAME(fkc.parent_object_id,
fkc.parent_column_id) AS ColumnName,
OBJECT_NAME (fk.referenced_object_id) AS ReferenceTableName,
COL_NAME(fkc.referenced_object_id,
fkc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.OBJECT_ID = fkc.constraint_object_id
order by IsNotForReplication

A snapshot of the outcome below:

FK Details

%d bloggers like this: