SQL Server 2008r2，导出一个数据库的所有表及字段，字段属性，包括是否标识，是否主键，字段类型，长度，是否允许空，默认值等

Question

SQL Server 2008r2，有没有办法导出一个数据库的所有表及字段，字段属性，包括是否标识，是否主键，字段类型，长度，是否允许空，默认值等？感谢！

Answer 1

右键数据库--任务--生成脚本，根据向导提示操作

Answer 2

Hi KZhj,

有，方法不少，推荐常见的2个：

1. 备份。

怎么备份SQL Server 2008数据库

2. 脚本导出-结构和数据。

怎么导出Sql Server数据库表结构和数据的脚本

Regards,

Moonlight

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 3

生成脚本

USE [test]
GO
/****** Object:  Table [dbo].[tt]    Script Date: 2016/6/28 15:37:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tt](
[id] [int] NULL,
[NAME] [nvarchar](600) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ttchar]    Script Date: 2016/6/28 15:37:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ttchar](
[id] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ttindex]    Script Date: 2016/6/28 15:37:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ttindex](
[id] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ttnoindex]    Script Date: 2016/6/28 15:37:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ttnoindex](
[id] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](300) NULL,
 CONSTRAINT [cix_ttnoindex_id] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ttttty]    Script Date: 2016/6/28 15:37:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ttttty](
[id] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [nvarchar](100) NULL,
[ProductName] [nvarchar](100) NULL,
[SupplierID] [int] NULL,
[CategoryID] [int] NULL,
[Unit] [nvarchar](100) NULL,
[Price] [int] NULL,
[intime] [datetime2](7) NULL,
PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[varcharmax]    Script Date: 2016/6/28 15:37:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[varcharmax](
[id] [int] IDENTITY(1,1) NOT NULL,
[tt] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

---

Love SQL

Answer 4

With A as
(
 SELECT
 FTbName = D.name,
 FTbExplain = Case When A.colorder=1 Then isnull(F.value,'') Else ''
 End,
 FEntryNo = A.colorder,
 FieldName = A.name,
 FieldExplain = isnull(G.[value],''),
 FIdentity = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '是'Else ''
 End,
 FKey = 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 ''
 end,
 FType = B.name,
 FBits = A.Length,
 FLendgh = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
 FDecimal = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
 FIsNull = Case When A.isnullable=1 Then '允许' Else ''
 End,
 FDeVales = isnull(E.Text,'')
 FROM sys.syscolumns AS a LEFT OUTER JOIN
 sys.systypes AS b ON a.xusertype = b.xusertype INNER JOIN
 sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
 sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
 sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
 sys.extended_properties AS f ON d.id = f.major_id AND f.minor_id = 0               
)
Select * from A
WHERE a.FTbName='所有表'
ORDER BY  FEntryNo

查询没有数据出来

Answer 5

去掉

WHERE a.FTbName='所有表'

那是查具体表的

---

http://feiyun0112.cnblogs.com/

Answer 6

是数据字典吗？参考：

SELECT     
	表名       = d.name,
	字段序号   = a.colorder,    
	字段名称   = a.name, 
	是否标识   = 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 '' end,    
	是否许空   = case when a.isnullable=1 then '√'else '' end,  
	类型       = b.name,  
	长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),  
	小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),    
	占用字节数 = a.length,    
	默认值     = isnull(e.text,''),    
	字段说明   = isnull(g.[value],'')    
FROM syscolumns a with(nolock)
left join systypes b with(nolock) on a.xusertype=b.xusertype    
inner join sysobjects d with(nolock) on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'    
left join syscomments e with(nolock) on a.cdefault=e.id    
left join sys.extended_properties g with(nolock) on a.id=g.major_id and a.colid=g.minor_id      
WHERE d.uid = 1 /*schema 为 dbo*/