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

  • 问题

  • SQL Server 2008r2,有没有办法导出一个数据库的所有表及字段,字段属性,包括是否标识,是否主键,字段类型,长度,是否允许空,默认值等?感谢!
    2016年6月28日 3:18

答案

  • 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.


    2016年6月28日 7:38
  • 生成脚本

    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

    2016年6月28日 7:38
  • 是数据字典吗?参考:

    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*/

    • 已标记为答案 kZhj 2016年7月12日 10:07
    2016年6月29日 9:40

全部回复

  • 右键数据库--任务--生成脚本,根据向导提示操作
    2016年6月28日 4:13
  • 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.


    2016年6月28日 7:38
  • 生成脚本

    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

    2016年6月28日 7:38
  • 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

    查询没有数据出来

    2016年6月28日 8:06
  • 去掉

    WHERE a.FTbName='所有表'

    那是查具体表的


    http://feiyun0112.cnblogs.com/

    2016年6月29日 2:51
  • 是数据字典吗?参考:

    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*/

    • 已标记为答案 kZhj 2016年7月12日 10:07
    2016年6月29日 9:40