July 17, 2013 at 8:20 am
hi
i am creating script for non clustered ,
i need to see of particular index on column on particular table exists or not
July 17, 2013 at 8:23 am
select * from sys.indexes
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2013 at 8:26 am
that will show all the index , i need to know particular index
if exist(
July 17, 2013 at 8:34 am
riya_dave (7/17/2013)
that will show all the index , i need to know particular indexif exist(
Do you know how to use a where clause?
if exists(select * from sys.indexes WHERE ...)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2013 at 6:46 pm
CREATE PROCEDURE [dbo].[UTIL_Detail_index_Stats]
@table_name sysname
AS
BEGIN
SET NOCOUNT ON
/*
EXEC [dbo].[UTIL_Detail_index_Stats] 'YourTableName'
*/
----------------------------------------------------------------------------------
-- ******VARIABLE DECLARATIONS******
----------------------------------------------------------------------------------
DECLARE @IndexTable TABLE
(
[Database] sysname, [Table] sysname, [Index Name] sysname NULL, index_id INT,
[object_id] INT, [Index Type] VARCHAR(20), [Alloc Unit Type] VARCHAR(20),
[Avg Frag %] decimal(5,2), [Row Ct] bigint, [Stats Update Dt] datetime
)
DECLARE @dbid INT --Database id for current database
DECLARE @objectid INT --Object id for table being analyzed
DECLARE @indexid INT --Index id for the target index for the STATS_DATE() function
----------------------------------------------------------------------------------
-- ******VARIABLE ASSIGNMENTS******
----------------------------------------------------------------------------------
SELECT @dbid = DB_ID(DB_NAME())
SELECT @objectid = OBJECT_ID(@table_name)
----------------------------------------------------------------------------------
-- ******Load @IndexTable with Index Metadata******
----------------------------------------------------------------------------------
INSERT INTO @IndexTable
(
[Database], [Table], [Index Name], index_id, [object_id],
[Index Type], [Alloc Unit Type], [Avg Frag %], [Row Ct]
)
SELECT
DB_NAME() AS "Database",
@table_name AS "Table",
SI.NAME AS "Index Name",
IPS.index_id, IPS.OBJECT_ID, --These fields included for joins only
IPS.index_type_desc, --Heap, Non-clustered, or Clustered
IPS.alloc_unit_type_desc, --In-row data or BLOB data
CAST(IPS.avg_fragmentation_in_percent AS decimal(5,2)),
IPS.record_count
FROM sys.dm_db_index_physical_stats (@dbid, @objectid, NULL, NULL, 'sampled') IPS
LEFT JOIN sys.sysindexes SI ON IPS.OBJECT_ID = SI.id AND IPS.index_id = SI.indid
WHERE IPS.index_id <> 0
----------------------------------------------------------------------------------
-- ******ADD STATISTICS INFORMATION******
----------------------------------------------------------------------------------
DECLARE curIndex_ID CURSOR FOR
SELECT I.index_id
FROM @IndexTable I
ORDER BY I.index_id
OPEN curIndex_ID
FETCH NEXT FROM curIndex_ID INTO @indexid
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE @IndexTable
SET [Stats Update Dt] = STATS_DATE(@objectid, @indexid)
WHERE [object_id] = @objectid AND [index_id] = @indexid
FETCH NEXT FROM curIndex_ID INTO @indexid
END
CLOSE curIndex_ID
DEALLOCATE curIndex_ID
----------------------------------------------------------------------------------
-- ******RETURN RESULTS******
----------------------------------------------------------------------------------
SELECT I.[Database], I.[Table], I.[Index Name], "Index Type"=
CASE I.[Index Type]
WHEN 'NONCLUSTERED INDEX' THEN 'NCLUST'
WHEN 'CLUSTERED INDEX' THEN 'CLUST'
ELSE 'HEAP'
END,
I.[Avg Frag %], I.[Row Ct],
CONVERT(VARCHAR, I.[Stats Update Dt], 120) AS "Stats Dt"
FROM @IndexTable I
ORDER BY I.[Index Type], I.[index_id]
END
July 17, 2013 at 9:40 pm
sp_helpindex <objectname> will help you
July 18, 2013 at 5:22 am
You can use below queries to find the index present on a specific table:
sp_helpindex 'table_name'
OR
select i.name, i.type_desc
from sys.indexes I
join sys.tables o ON i.object_id = o.object_id
WHERE o.name = 'table_name'
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 18, 2013 at 5:42 am
riya_dave (7/17/2013)
hii am creating script for non clustered ,
i need to see of particular index on column on particular table exists or not
Use object explorer in SSMS.
It's not usually as simple as this, however. You need to know the usage patterns of existing indexes and index recommendations before adding new indexes.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply