使用示例:
sp_table tableName ,ColumnLike (列关键字) --返回表结构信息
sp_helptext 视图|函数|存储过程 --返回对象代码
/****** Object: StoredProcedure [dbo].[sp_table] Script Date: 10/09/2011 10:20:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sp_table]
(@tableName varchar(200)
,@ColumnLike varchar(200)=NULL)
as
--/********************************************
--根据表名得到表信息,包括字段说明
--*********************************************/
--DECLARE @tableName VARCHAR(200);
--DECLARE @ColumnLike VARCHAR(200);
--SET @tableName='purchase'
--SET @ColumnLike=NULL;
--如果表明不存在,就直接选出相似表
if not exists( select 1 from sysobjects where id = object_id(@tableName) and type = 'U')
begin
select name from sysobjects where name like '%'+@tableName + '%' and type = 'U'
return
end
--筛选相似列明
if(@ColumnLike is null)
set @ColumnLike = ''
declare @ColumnTable table(cName varchar(200))
insert @ColumnTable(cName)
select a.name from syscolumns a,sysobjects d
where a.id=d.id
and d.name = @tableName and a.name like '%'+ @ColumnLike +'%'
--查询表结构信息
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
字段说明=isnull(g.[value],''),
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else ''