获取sql server数据库中所有用户表名

http://www.cnblogs.com/justwannaloveyou/archive/2010/11/09/1873021.html

获取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