Check if Index exists
-
Has anyone written tsql code to check if an index exists for a table and if so drop the index . Something like what Isindexed is used for . Any help will be greatly appreciated.
TIA
-
Do you need that based solely on the name or on the columns used (and their order)?
-
Have u ever tried looking at sysindexes table. That will have the information that u are looking for.
Hope this helps.
Thanks
Sreejith
-
Thanks.
RGR'us - What i am trying to do is in a DTS pckg if an index exists for a table on a specific column first "drop it" do the data load then once thats done, again check if index exists and if not create it. Any suggestions please?
TIA
-
Fetch all indexes using this script
SELECT
t.[name],
i.[name] as IndexName
FROM
SYS.INDEXES AS i WITH (NOLOCK)
INNER JOIN
SYS.TABLES AS t WITH (NOLOCK)
ON
i.[object_id] = t.[object_id]
INNER JOIN
SYS.INDEX_COLUMNS AS ic WITH (NOLOCK)
ON
i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
WHERE
t.[type] = 'U'
AND t.Is_MS_Shipped = 0
AND i.Is_Hypothetical = 0
write a for loop
disable the index using a dynamic sql
ALTER INDEX <> ON <
> DISABLE
and enable them in the same way
ALTER INDEX <> ON <
> REBUILD
-
Are you using sql 2005? somehow its doesnt work in sql 2000 as these system tables dont exists there.
TIA
-
Change the schema from sys. to dbo.
-
try this
select distinct o.[name],case i.indid when 1 then 'PrimaryKey' else i.[name] end as IndexName
from sysindexes i
inner join sysobjects o on
i.id=o.id
inner join sysindexkeys k on
k.id=o.id and k.indid=i.indid
where o.xtype='U' and i.[name] not like '_WA_Sys_%' and o.[name]<>'dtproperties'
-
Oops.. I forgot the rest of the changes to the system tables
!!Thanx Gopi!
-
CREATE proc drop_index_if_exists-- ===================================================================
-- Author: Sergei Krasnov
-- Create date: 1/27/2009
-- Description: delete index/PK if exists
-- ===================================================================
(
@idx_name sysname,
@schema_name sysname,
@tbl_name sysname
)
AS
BEGIN
DECLARE @sql_cmd nvarchar(max);
DECLARE @is_pk bit
select @is_pk = is_primary_key from sys.tables tbl
inner join sys.indexes idx on
tbl.object_id = idx.object_id and
idx.name = @idx_name and
tbl.name = @tbl_name and
SCHEMA_NAME (tbl.schema_id) = @schema_name
set @sql_cmd =
case
when @is_pk = 0 then N'DROP index ['+ @idx_name +'] ON ['+@schema_name+'].['+@tbl_name+']'
when @is_pk = 1 then N'alter table ['+@schema_name+'].['+@tbl_name+'] drop constraint ['+@idx_name+']'
end
print @sql_cmd
exec (@sql_cmd)
END;
GO
--how to use:
--exec META.drop_index_if_exists 'PK_ActionLocationTypeMap', 'META', 'ActionLocationTypeMap'
-
Hi Sergei,
1st, you probably posted on wrong forum: sys.tables works for SQL2005, not SQL2000.
2nd, constraints happen to be not only primary.
_____________
Code for TallyGenerator -
I'm sorry, you're definitely right. wrong forum 🙂 and script is not completed, just a stub. Thank you.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply