获取sql server数据库中所有用户表名及在sql server中怎样用sql得到库中所有的表名以及表的结构(列名和数据类型)
1、获取sql server数据库中所有用户表名
得到所有用户表：（其中xtype:U用户表；V视图；P存储过程
sql="select id,name from sysobjects where xtype='U'and name<>'dtproperties' order by name"
得到某个表中所有字段名：
SELECT syscolumns.name AS ColumnName,systypes.name AS Type, syscolumns.length,syscolumns.isnullable
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE (sysobjects.xtype = 'U')
AND (sysobjects.name <> 'dtproperties')
AND (sysobjects.name = 'TableName')
AND (systypes.name <> 'sysname')
AND (systypes.status <> 3) --//3是排除自定义的数据类型
GROUP BY syscolumns.name, sysobjects.name, syscolumns.xtype,systypes.name, syscolumns.length,syscolumns.isnullable
获得某个存储过程关联的对象：
select name from sysobjects where id in(
SELECT distinct bb.depid as kk
FROM sysobjects
JOIN sysdepends bb ON bb.id = sysobjects.id
where sysobjects.name='SPName'
)
<说明：所得的表只是与select有关的表，比如update、insert等用到的表并查找不出来>
2、在sql server中怎样用sql得到库中所有的表名,以及表的结构(列名和数据类型)
CREATE PROCEDURE gettableinfo
/*@TableName varchar(32)*/
AS
/*创建临时表*/
create table #TableFields(
tableName varchar(32),
fieldname varchar(32),
fieldtype varchar(32),
fieldlength varchar(32),
scale varchar(32),
des varchar(256),
defaultvalue varchar(32),
CanNULL varchar(32)
)
/* 声明游标*/
declare table_cur scroll cursor
for select sysobjects.name from sysobjects where sysobjects.xtype = 'U'
for update of sysobjects.name
/*声明临时表名*/
declare @TName varchar(32)
/* 打开游标*/
open table_cur
fetch next from table_cur into @TName
while @@fetch_status=0 begin
SELECT sysobjects.name AS tableName, syscolumns.name AS filedname,
systypes.name AS fieldtype, syscolumns.length,
syscolumns.scale
into #FiledInfo_Master
FROM syscolumns INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE (sysobjects.xtype = 'U') AND (systypes.name <> 'sysname') and sysobjects.name=@TName
/*得到字段描述*/
SELECT objname as filedname ,value into #FiledInfo
FROM ::fn_listextendedproperty('MS_Description', 'user',
'dbo', 'table', @TName,
'column', DEFAULT)
/*得到字段缺省值*/
SELECT objname as filedname, value as defaultvalue
into #FiledInfo2
FROM ::fn_listextendedproperty('DefaultValue', 'user',
'dbo', 'table', @TName,
'column', DEFAULT)
/*得到字段是否可为空*/
SELECT objname as filedname, value as CanNULL
into #FiledInfo3
FROM ::fn_listextendedproperty('MS_AllowBlanks', 'user',
'dbo', 'table', @TName,
'column', DEFAULT)
/*联结字段描述和属性*/
insert into #TableFields
SELECT cast(#FiledInfo_Master.tableName as varchar(32)) ,
cast(#FiledInfo_Master.filedname as varchar(32)),
cast(#FiledInfo_Master.fieldtype as varchar(32)),
cast(#FiledInfo_Master.length as varchar(32)),
cast(#FiledInfo_Master.scale as varchar(32)),
cast(#FiledInfo.[value] as varchar(256)),
cast(#FiledInfo2.defaultvalue as varchar(32)),
cast(#FiledInfo3.CanNULL as varchar(32))
FROM dbo.#FiledInfo_Master LEFT OUTER JOIN
dbo.#FiledInfo ON dbo.#FiledInfo_Master.filedname = dbo.#FiledInfo.FiledName
LEFT OUTER JOIN dbo.#FiledInfo2
ON dbo.#FiledInfo_Master.filedname = dbo.#FiledInfo2.FiledName
LEFT OUTER JOIN dbo.#FiledInfo3
ON dbo.#FiledInfo_Master.filedname = dbo.#FiledInfo3.FiledName
where
#FiledInfo_Master.tablename=@TName
fetch next from table_cur into @TName
--if exists (select * from dbo.sysobjects where id = object_id(N'#FiledInfo_Master') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table #FiledInfo_Master
--if exists (select * from dbo.sysobjects where id = object_id(N'#FiledInfo') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table #FiledInfo
--if exists (select * from dbo.sysobjects where id = object_id(N'#FiledInfo2') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table #FiledInfo2
drop table #FiledInfo3
end
select * from #TableFields
deallocate table_cur
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
