SQL2008常用方法

/*
作者:chelen
日期:2011-02-19
描述:
  獲取@Table的字段信息 for SQL2008
參數:
 @Table - 操作表名
 @Field - 要查看的字段,不輸入為全部字段
*/
create Procedure [dbo].[sp_ShowTable] (@Table NVarchar(776), @Field NVarchar(776)='')
As
 select @Table as TabName, value as [Description]
 from sys.extended_properties
 where major_id = OBJECT_ID(@Table) and minor_id = 0

 SELECT CASE IsNull(I.name, '') When '' Then '' Else '*' End as IsPK,
 -- Object_Name(A.id) as t_name,
  A.name as Name,
  T.name as DataType,
  (case  when A.autoval is null then '' else '1' end) as AutoVal, 
  A.Length,
  CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '') WHEN '' Then Cast(A.prec as varchar) ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar) END as Scale,
  IsNull(SubString(M.text, 1, 254), '') as DefaultValue,
  A.isnullable as IsNullAble,
  isnull(g.[value],'') AS [Description],
  --约束的信息

  isnull(x.[LinkKey],'') as [LinkKey], 

 

  isnull(x.[CONSTRAINT],'') as [Constraint]   
 FROM Syscolumns as A
 JOIN Systypes as T ON (A.xType = T.xUserType AND A.Id = Object_id(@Table) )
 LEFT JOIN ( SysIndexes as I JOIN Syscolumns as A1 ON ( I.id = A1.id and A1.id = object_id(@Table) and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) ) ON ( A.id = I.id AND A.name = index_col(@Table, I.indid, A1.colid) )
 LEFT JOIN SysComments as M ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
 ---- Sql2000
 --left join sysproperties g on a.id=g.id AND a.colid = g.smallid
 -- sql2008
    LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id  
    -- 约束的信息
    left join
  (
  select
   D.id, D.colid,
   A.name as [TabName],
   B.name as [CONSTRAINT],
   D.name as [ForeignKey],
   (case
    when E.name is not null and F.name is not null then E.name + '.' + F.name
    else ''
   end) as [LinkKey]
  from (select ID, name from sysobjects where xtype=N'U') A       -- 关联所有者（表）
  inner join (select * from sysobjects where xtype=N'F') B on B.parent_obj = A.id  -- 关联名称
  inner join sysforeignkeys C on C.constid = B.id          -- 关联关系表(辅助表)
  inner join syscolumns D on D.id = A.id and D.colid = C.fkey       -- 所有者(表).外键字段
  left join (select ID, name from sysobjects where xtype=N'U') E on E.id = C.rkeyid -- 主键表
  left join syscolumns F on F.id = E.id and F.colid = C.rkey       -- 主键字段   
  ) X on X.id = A.id AND X.colid = A.colid   
 Where A.Name=@Field Or IsNull(@Field,'')=''
 ORDER BY A.Colid ASC

 

/*
作者:chelen
日期:2006-06-07
作用描述:獲取@TableName的字段列表,@AliasName為別名

*/
create   Procedure [dbo].[sp_Fields] @TableName nvarchar(776), @AliasName nvarchar(776)=''
As
 Declare @Result varchar(7760)
 Declare @FieldName Varchar(776)
 Declare @S1 varchar(776)

 if @AliasName <> ''
  SET @S1 = @AliasName + '.'
 else
  Set @S1 = ''
 
 SET @Result = ''
 DECLARE Tmp_cursor CURSOR FOR
 select Name from syscolumns where ID = Object_id( @TableName )
 order by colid
 OPEN Tmp_cursor

 FETCH NEXT FROM TMP_cursor
 INTO @FieldName

 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @Result = @Result + ',' + @S1 + @FieldName
  FETCH NEXT FROM Tmp_cursor
  INTO @FieldName
 END
 
 CLOSE Tmp_cursor
 DEALLOCATE Tmp_cursor

 if @Result <> ''
  Set @Result = Substring(@Result,2,Len(@Result))
 else
  Set @Result = 'Not Fields Info'

 Print @Result 

 

/*
作者:chelen
日期:2007-01-03
描述:
 返回某字段(參數)在各表(存儲過程、視圖)的信息
參數:
 @Field - 要查看的字段
*/
create Procedure [dbo].[SP_ShowField](@Field Varchar(776))
As
 Select
  OBJECT_NAME(C.ID) Owner,
  --表的信息
  ISNULL(y.value,'') as TabDesc,     
  C.Name as ColName,  
  (case when OBJECTPROPERTY(C.ID, N'IsTable') = 1 then N'Table'
       when OBJECTPROPERTY(C.ID, N'IsView') = 1 then N'View'
       when OBJECTPROPERTY(C.ID, N'IsProcedure') = 1 then 'Procedure'
       when OBJECTPROPERTY(C.ID, N'IsInlineFunction') = 1 then 'Function'
              else N'OTH'
  end) as OwnerType,
  T.Name DataType,
  (case  when C.autoval is null then '' else '1' end) as AutoValue,
  C.length, --長度
  C.xprec,  --精确度
  C.xscale, --小數位,
  (case C.isnullable when 0 then '' else '1' end ) as IsNullAble,
  IsNull(SubString(M.text, 1, 254), '') as DefaultValue,
  isnull(g.[value],'') AS [Description],
  --约束的信息
  isnull(x.[LinkKey],'') as [LinkKey], 
  isnull(x.[CONSTRAINT],'') as [Constraint]      
 from syscolumns C
 left join (select * from SysTypes where name <> 'sysname') T on T.xtype = C.xtype
 ---- Sql2000
 --left join sysproperties g on c.id=g.id AND c.colid = g.smallid
 -- sql2008
    LEFT JOIN sys.extended_properties g ON c.id = g.major_id AND c.colid = g.minor_id  
 
 LEFT JOIN SysComments as M ON ( M.id = C.cdefault and ObjectProperty(C.cdefault, 'IsConstraint') = 1 )
    -- 约束的信息
    left join
  (
  select
   D.id, D.colid,
   A.name as [TabName],
   B.name as [CONSTRAINT],
   D.name as [ForeignKey],
   (case
    when E.name is not null and F.name is not null then E.name + '.' + F.name
    else ''
   end) as [LinkKey]
  from (select ID, name from sysobjects where xtype=N'U') A       -- 关联所有者（表）
  inner join (select * from sysobjects where xtype=N'F') B on B.parent_obj = A.id  -- 关联名称
  inner join sysforeignkeys C on C.constid = B.id          -- 关联关系表(辅助表)
  inner join syscolumns D on D.id = A.id and D.colid = C.fkey       -- 所有者(表).外键字段
  left join (select ID, name from sysobjects where xtype=N'U') E on E.id = C.rkeyid -- 主键表
  left join syscolumns F on F.id = E.id and F.colid = C.rkey       -- 主键字段   
  ) X on X.id = C.id AND X.colid = C.colid     
 -- 表的信息
 left join (select * from sys.extended_properties where minor_id = 0) y on y.major_id = C.id  
 where OBJECTPROPERTY(C.ID, N'IsTable')=1 And C.name like @Field  --字段(參數)名
 order by OwnerType,Owner

/*
描述:
 根據描述關鍵字返回相關的字段, 仿 sp_ShowField
歷史:
 2009-05-18 Chelen 创建

*/
create Procedure [dbo].[SP_ShowDesc](@Description Varchar(776))
As
 Select
  OBJECT_NAME(C.ID) Owner,
  C.Name as ColName,  
  (case when OBJECTPROPERTY(C.ID, N'IsTable') = 1 then N'Table'
       when OBJECTPROPERTY(C.ID, N'IsView') = 1 then N'View'
       when OBJECTPROPERTY(C.ID, N'IsProcedure') = 1 then 'Procedure'
       when OBJECTPROPERTY(C.ID, N'IsInlineFunction') = 1 then 'Function'
              else N'OTH'
  end) as OwnerType,
  T.Name DataType,
  (case  when C.autoval is null then '' else '1' end) as AutoValue,
  C.length, --長度
  C.xprec,  --精确度
  C.xscale, --小數位,
  (case C.isnullable when 0 then '' else '1' end ) as IsNullAble,
  IsNull(SubString(M.text, 1, 254), '') as DefaultValue,
  isnull(g.[value],'') AS [Description],
  --约束的信息
  isnull(x.[LinkKey],'') as [LinkKey], 
  isnull(x.[CONSTRAINT],'') as [Constraint]         
 from syscolumns C
 left join (select * from SysTypes where name <> 'sysname') T on T.xtype = C.xtype
 ---- Sql2000
 --left join sysproperties g on c.id=g.id AND c.colid = g.smallid
 -- sql2008
    LEFT JOIN sys.extended_properties g ON c.id = g.major_id AND c.colid = g.minor_id   
 LEFT JOIN SysComments as M ON ( M.id = C.cdefault and ObjectProperty(C.cdefault, 'IsConstraint') = 1 )
    -- 约束的信息
    left join
  (
  select
   D.id, D.colid,
   A.name as [TabName],
   B.name as [CONSTRAINT],
   D.name as [ForeignKey],
   (case
    when E.name is not null and F.name is not null then E.name + '.' + F.name
    else ''
   end) as [LinkKey]
  from (select ID, name from sysobjects where xtype=N'U') A       -- 关联所有者（表）
  inner join (select * from sysobjects where xtype=N'F') B on B.parent_obj = A.id  -- 关联名称
  inner join sysforeignkeys C on C.constid = B.id          -- 关联关系表(辅助表)
  inner join syscolumns D on D.id = A.id and D.colid = C.fkey       -- 所有者(表).外键字段
  left join (select ID, name from sysobjects where xtype=N'U') E on E.id = C.rkeyid -- 主键表
  left join syscolumns F on F.id = E.id and F.colid = C.rkey       -- 主键字段   
  ) X on X.id = C.id AND X.colid = C.colid      
 where OBJECTPROPERTY(C.ID, N'IsTable')=1 And Convert(varchar, isnull(g.[value],'')) like '%' + @Description + '%'
 order by OwnerType,Owner

/*
作者:chelen
日期:2007-01-03
描述:
 返回某字段(參數)在各表(存儲過程、視圖)的信息
參數:
 @LinkKey - 要查看的被关联字段
*/
create Procedure [dbo].[SP_ShowLink](@LinkKey Varchar(776))
As
 Select
  OBJECT_NAME(C.ID) Owner,
  --表的信息
  ISNULL(y.value,'') as TabDesc,   
  C.Name as ColName,  
  (case when OBJECTPROPERTY(C.ID, N'IsTable') = 1 then N'Table'
       when OBJECTPROPERTY(C.ID, N'IsView') = 1 then N'View'
       when OBJECTPROPERTY(C.ID, N'IsProcedure') = 1 then 'Procedure'
       when OBJECTPROPERTY(C.ID, N'IsInlineFunction') = 1 then 'Function'
              else N'OTH'
  end) as OwnerType,
  T.Name DataType,
  (case  when C.autoval is null then '' else '1' end) as AutoValue,
  C.length, --長度
  C.xprec,  --精确度
  C.xscale, --小數位,
  (case C.isnullable when 0 then '' else '1' end ) as IsNullAble,
  IsNull(SubString(M.text, 1, 254), '') as DefaultValue,
  isnull(g.[value],'') AS [Description],
  --约束的信息
  isnull(x.[LinkKey],'') as [LinkKey], 
  isnull(x.[CONSTRAINT],'') as [Constraint]
 from syscolumns C
 left join (select * from SysTypes where name <> 'sysname') T on T.xtype = C.xtype
 ---- Sql2000
 --left join sysproperties g on c.id=g.id AND c.colid = g.smallid
 -- sql2008
    LEFT JOIN sys.extended_properties g ON c.id = g.major_id AND c.colid = g.minor_id  
 
 LEFT JOIN SysComments as M ON ( M.id = C.cdefault and ObjectProperty(C.cdefault, 'IsConstraint') = 1 )
    -- 约束的信息
    left join
  (
  select
   D.id, D.colid,
   A.name as [TabName],
   B.name as [CONSTRAINT],
   D.name as [ForeignKey],
   (case
    when E.name is not null and F.name is not null then E.name + '.' + F.name
    else ''
   end) as [LinkKey]
  from (select ID, name from sysobjects where xtype=N'U') A       -- 关联所有者（表）
  inner join (select * from sysobjects where xtype=N'F') B on B.parent_obj = A.id  -- 关联名称
  inner join sysforeignkeys C on C.constid = B.id          -- 关联关系表(辅助表)
  inner join syscolumns D on D.id = A.id and D.colid = C.fkey       -- 所有者(表).外键字段
  left join (select ID, name from sysobjects where xtype=N'U') E on E.id = C.rkeyid -- 主键表
  left join syscolumns F on F.id = E.id and F.colid = C.rkey       -- 主键字段   
  ) X on X.id = C.id AND X.colid = C.colid   
 -- 表的信息
 left join (select * from sys.extended_properties where minor_id = 0) y on y.major_id = C.id
 where OBJECTPROPERTY(C.ID, N'IsTable')=1 And isnull(x.[LinkKey],'') like @LinkKey  --字段(參數)名
 order by OwnerType,Owner

 

/*
作者:chelen
日期:2011-02-20
描述:
  獲取@Table被关联的信息 for SQL2008
*/
create procedure [dbo].[sp_ShowConstraint]
 (
  @Table NVarchar(776) -- 被关联的表名
 )
as  
 select @Table as TabName, value as [Description]
 from sys.extended_properties
 where major_id = OBJECT_ID(@Table) and minor_id = 0

 select
  --D.id, D.colid,
  --E.name,   
  F.name as [Column], 
  A.name as [TabName],
  D.name as [ForeignKey],
  G.value as [Description],
  B.name as [CONSTRAINT]   
 from (select ID, name from sysobjects where xtype=N'U') A       -- 关联所有者（表）
 inner join (select * from sysobjects where xtype=N'F') B on B.parent_obj = A.id  -- 关联名称
 inner join sysforeignkeys C on C.constid = B.id          -- 关联关系表(辅助表)
 inner join syscolumns D on D.id = A.id and D.colid = C.fkey       -- 所有者(表).外键字段
 left join (select ID, name from sysobjects where xtype=N'U') E on E.id = C.rkeyid -- 主键表
 left join syscolumns F on F.id = E.id and F.colid = C.rkey       -- 主键字段   
 left join (select * from sys.extended_properties where minor_id = 0) G on G.major_id = A.id -- 取表名
 where E.name = @Table
 order by A.name, D.colid