/*eg:
--调用该过程实例
--1 创建临时表
IF OBJECT_ID('tempdb..#index_sql_text') IS NOT NULL
DROP TABLE #index_sql_text
CREATE TABLE #index_sql_text(
tablename varchar(700),
index_name VARCHAR(200),
index_description VARCHAR(200),
index_keys VARCHAR(200),
create_sql_test VARCHAR(500),
drop_sql_test VARCHAR(500)
)
--2 执行过程向临时表写入数据
EXEC pro_index_sql_text 'ED_RetailPriceIndex','PK_ED_RetailPriceIndex'
--3 使用临时表数据
SELECT * FROM #index_sql_text
*/
IF EXISTS (SELECT TOP 1 1 FROM sys.objects where type = 'P' AND name = 'pro_index_sql_text')
DROP PROCEDURE pro_index_sql_text
GO
CREATE PROCEDURE pro_index_sql_text
@objname nvarchar(776), --表名：该变量不能为空，所以不默认指定
@indexname nvarchar(200) = NULL --索引名：当指定索引时返回该索引数据，当没有指定值时默认返回表的所有索引
AS
/*
最后修改日期：2016-12-08
说明：给定表名，返回对应表的索引，主键，唯一约束键的删除及新建脚本
参数：@objname-表名，必须指定，不能包含架构名如 dbo.table(有问题)，table(正确)
@indexname-索引名：当指定索引时返回该索引数据，当没有指定值时默认返回表的所有索引
--declare @objname varchar(776)
--select @objname = 'TEST_TABLE'
*/
--1 判断过程外面是否已经创建，创建临时表 #index_sql_text 存放最后结果
IF OBJECT_ID('tempdb..#index_sql_text') IS NULL
BEGIN
CREATE TABLE #index_sql_text(
tablename varchar(700),
index_name VARCHAR(200),
index_description VARCHAR(200),
index_keys VARCHAR(200),
create_sql_test VARCHAR(500),
drop_sql_test VARCHAR(500)
)
END
--2 声明变量并检查库表关系(借鉴系统过程 sp_helpindex)
declare @objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid int, -- the filegroup id of an index
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@dbname sysname,
@ignore_dup_key bit,
@is_unique bit,
@is_hypothetical bit,
@is_primary_key bit,
@is_unique_key bit,
@auto_created bit,
@no_recompute bit
--@dbname 赋值
select @dbname = parsename(@objname,3)
if @dbname is null
select @dbname = db_name()
-- Check to see the the table exists and initialize @objid.
select @objid = object_id(@objname)
if @objid is NULL
begin
raiserror(15009,-1,-1,@objname,@dbname)
return
end
--3 声明游标(包括该表的索引情况)
declare ms_crs_ind cursor local static for
select
i.index_id,
i.data_space_id,
i.name,
i.ignore_dup_key,
i.is_unique,
i.is_hypothetical,
i.is_primary_key,
i.is_unique_constraint,
s.auto_created,
s.no_recompute
from sys.indexes i
join sys.stats s on i.object_id = s.object_id and i.index_id = s.stats_id
where i.object_id = @objid --限制对象名称
and (i.name = @indexname OR @indexname IS NULL) --限制索引名称
--打开游标
open ms_crs_ind
--获取游标数据
fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute
-- IF NO INDEX, QUIT
if @@fetch_status < 0
begin
deallocate ms_crs_ind
raiserror(15472,-1,-1,@objname) -- Object does not have any indexes.
return
end
--4 新建存放基础数据临时表 #spindtab
IF OBJECT_ID('tempdb..#spindtab') IS NOT NULL
DROP TABLE #spindtab
CREATE TABLE #spindtab
(
index_name sysname collate database_default NOT NULL,
index_id int,
ignore_dup_key bit,
is_unique bit,
is_hypothetical bit,
is_primary_key bit,
is_unique_key bit,
auto_created bit,
no_recompute bit,
groupname sysname collate database_default NULL,
index_keys nvarchar(2126) collate database_default NOT NULL -- see @keys above for length descr
)
-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status = 0
begin
-- First we'll figure out what the keys are.
declare @i int, @thiskey nvarchar(131) -- 128+3
/*
返回索引信息及索引字段的升降序
INDEXKEY_PROPERTY ( object_ID ,index_ID ,key_ID ,property )
property 要返回其信息的属性的名称。
IsDescending
存储索引列的排序顺序。
1 = 降序 0 = 升序
*/
select @keys = index_col(@objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
--select @keys = @keys + '(-)'
select @keys = @keys + ' desc'
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
--select @thiskey = @thiskey + '(-)'
select @thiskey = @thiskey + ' desc'
while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
--select @thiskey = @thiskey + '(-)'
select @thiskey = @thiskey + ' desc'
end
select @groupname = null
select @groupname = name from sys.data_spaces where data_space_id = @groupid
-- INSERT ROW FOR INDEX
insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys)
-- Next index 获取游标下一条数据
fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute
end
deallocate ms_crs_ind
--5 结果集拼接并写入临时表
insert into #index_sql_text
select
@objname,
'index_name' = index_name,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when index_id = 1 then 'clustered' else 'nonclustered' end
+ case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end
+ case when is_unique <>0 then ', unique' else '' end
+ case when is_hypothetical <>0 then ', hypothetical' else '' end
+ case when is_primary_key <>0 then ', primary key' else '' end
+ case when is_unique_key <>0 then ', unique key' else '' end
+ case when auto_created <>0 then ', auto create' else '' end
+ case when no_recompute <>0 then ', stats no recompute' else '' end
+ ' located on ' + groupname),
'index_keys' = index_keys,
'create_sql_test' = CASE WHEN is_primary_key = 1 THEN 'ALTER TABLE ' + @objname+ ' ADD CONSTRAINT ' + index_name + ' PRIMARY KEY '
+CASE WHEN index_id =1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END
+'('+index_keys+');'
WHEN is_primary_key = 0 AND is_unique_key = 1 THEN 'ALTER TABLE ' + @objname+ ' ADD CONSTRAINT ' + index_name
+CASE WHEN is_unique =0 THEN '' ELSE ' UNIQUE ' END
+CASE WHEN index_id =1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END
+'('+index_keys+');'
WHEN is_primary_key = 0 AND is_unique_key = 0 THEN 'CREATE ' +
CASE WHEN is_unique =0 THEN '' ELSE 'UNIQUE ' END +
CASE WHEN index_id =1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END + 'INDEX '+index_name+
' on ' + @objname+'('+index_keys+');'
END,
'drop_sql_test' = CASE WHEN is_primary_key = 1 THEN 'ALTER TABLE ' + @objname+ ' DROP CONSTRAINT ' + index_name+';'
WHEN is_primary_key = 0 AND is_unique_key = 1 THEN 'ALTER TABLE ' + @objname
+ ' DROP CONSTRAINT ' + index_name+';'
WHEN is_primary_key = 0 AND is_unique_key = 0 THEN 'DROP INDEX ' + @objname
+'.'+ index_name+';'
END
from #spindtab
order by index_name
/*
--调试代码是方便查看，用于调度时不需要该语句
SELECT * FROM #index_sql_text
*/
