这是一款多功能的存储过程

 

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