Create ProceDure sp_GetChinaName
@TableName varchar(50)
As
Declare @Sql varchar(1000) ,
@a int,
@b int,
@i int,
@j int,
@m int,
@n int,
@YOrN1 int,
@YOrN2 int
--------------------------------------- --从系统中找Table ,字段
select '字段名称'= name,
'字段名称' = type_name(xusertype),
'长度' = length,
'Prec' = case when type_name(xtype) = 'uniqueidentifier' then xprec
else OdbcPrec(xtype, length, xprec) end,
'Scale' = OdbcScale(xtype,xscale),
'Param_order' = colid,
'Collation' = collation
into #System from syscolumns where id = object_id(@TableName) and number = 0 Order By name
Select 含有该字段的数据表=name
into #Columns from sysobjects where id in (select id From syscolumns where name=@TableName) and xtype='U' Order By name
Select @m=Count(*) From #System
Select @n=Count(*) From #Columns
---------------------------------------empbas
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'資料表檢索_userTable一階' AND type = 'U')
Select @YOrN1=1 Else Select @YOrN1=0
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'資料表檢索_userTable二階' AND type = 'U')
Select @YOrN2=1 Else Select @YOrN2=0
--------------------------------------
Select @Sql='Select * from 資料表檢索_userTable一階 Where 物件名稱='''+@TableName+''''
Select @Sql=@Sql+'Select 欄位順序,主鍵,欄位名稱,中文名稱,資料型別,長度,允許Null,用途 from 資料表檢索_userTable二階 Where 物件名稱='''+@TableName+''''
IF ((@YOrN1>0) And (@YOrN2>0))
Begin
Select @b=Count(*) From (Select * from 資料表檢索_userTable二階 Where 物件名稱=@TableName)b
Select @i=@b+Count(*) From (Select * from 資料表檢索_userTable一階 Where 物件名稱=@TableName)a
Select @j=Count(*) From ( Select * From 資料表檢索_userTable二階 Where 欄位名稱 like '%'+@TableName+'%' And 物件名稱 In (Select name from sysobjects where xtype='U' ))c
Select @a=@j+Count(*) From ( Select * From 資料表檢索_userTable二階 Where 中文名稱 like '%'+@TableName+'%' And 物件名稱 In (Select name from sysobjects where xtype='U' ))d
End
IF @i>0
Begin
Exec(@Sql)
IF @b<@m Select * from #System
End
Else IF @a>0
Begin
Select * From 資料表檢索_userTable二階 Where 欄位名稱 like '%'+@TableName+'%' And 物件名稱 In (Select name from sysobjects where xtype='U' )
Select * From 資料表檢索_userTable二階 Where 中文名稱 like '%'+@TableName+'%' And 物件名稱 In (Select name from sysobjects where xtype='U' )
IF @j<@n Select * From #Columns Order by 含有該欄位的資料表
End
Else
Begin
IF @m>0 Select * from #System
IF @n>0 Select * From #Columns
IF (@m+@n)=0 Print '輸入值沒有相關信息 , 或不在當前資料庫中!'
End
--------------------------------------------------------------------
declare @objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid smallint, -- 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
---------------------
create table #spindtab
(
index_name sysname collate database_default NOT NULL,
stats int,
groupname sysname collate database_default NOT NULL,
index_keys nvarchar(2126) collate database_default NOT NULL -- see @keys above for length descr
)
--------------------
select @objid = object_id(@TableName)
------------------
declare ms_crs_ind cursor local static for
select indid, groupid, name, status from sysindexes where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
open ms_crs_ind
fetch ms_crs_ind into @indid, @groupid, @indname, @status
---------------------
if @@fetch_status < 0
begin
deallocate ms_crs_ind
raiserror(15472,-1,-1) --'Object does not have any indexes.'
return (0)
end
---------------------
declare @k int, @thiskey nvarchar(131) -- 128+3
select @keys = index_col(@TableName, @indid, 1), @k = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'
select @thiskey = index_col(@TableName, @indid, @k)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @k, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @k = @k + 1
select @thiskey = index_col(@TableName, @indid, @k)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @k, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end
select @groupname = groupname from sysfilegroups where groupid = @groupid
-- INSERT ROW FOR INDEX
insert into #spindtab values (@indname, @status, @groupname, @keys)
-------------------------------------------------------------------
declare @empty varchar(1) select @empty = ''
declare @des1 varchar(35), -- 35 matches spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216
select
'索引名' = index_name,
'索引描述' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname),
'索引鍵值' = index_keys
from #spindtab
order by index_name