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