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