SQL2008常用方法

原创 2012年03月29日 17:11:11

/*
作者: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


 

Win2003服务器常用软件安装之Ms Sql Server2008安装图解

安装SQL2008的过程与SQL2005的程序基本一样,只不过在安装的过程中部分选项有所改变,当然如果只熟悉SQL2000安装的同志来说则是一个革命性的变动。---------------------...

sql2008常用服务帐户详解

NT Authority\System ,系统内置账号,对本地系统拥有完全控制权限;在工作组模式下,该账户不能网络资源;通常用于服务的运行,不需要密码。 NT Authority\Network...
  • dgtg77
  • dgtg77
  • 2011年10月31日 08:04
  • 1029

关于SQL Server2008中提供的9种常用的数据挖掘算法

在sql server2008中提供了9种常用的数据挖掘算法,这些算法用在不同数据挖掘的应用场景下,下面我们就各个算法逐个分析讨论。   1.决策树算法    决策树,又称判定树,是一种类...

SQL 2008数据库常用代码

  • 2012年09月18日 18:47
  • 138KB
  • 下载

SQL 2008 常用语句大全

  • 2014年05月12日 10:45
  • 100KB
  • 下载

SQL server 2008 安装时 安装程序支持规则“重新启动计算机”失败解决方法

1.时间:2017-05-09 20:30  YuanMxy 2.问题描述:SQL server 2008 安装时 安装程序支持规则“重新启动计算机”失败。   3.出现原因:注册表Pendi...
  • YuanMxy
  • YuanMxy
  • 2017年05月09日 21:32
  • 3421

SQL2008 常用 命令

  • 2013年05月07日 09:14
  • 138KB
  • 下载

sql server 2008数据库日志太大解决方法

环境: 数据文件.mdf为22G,日志文件.ldf为33G,用常规方法清除日志文件时,报错: USE [master] GO ALTER DATABASE CHIANG_SCM_DB SET REC...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL2008常用方法
举报原因:
原因补充:

(最多只允许输入30个字)