SQL Server 2005. Is there a sql query that will return a text field containing the same type of schema info as you would find in doing a right click table -> Script Table As -> Create To (or Alter To) from SQL Server Management Studio ?
SQL Server 2005.是否有一个SQL查询将返回一个文本字段,其中包含与执行右键单击表中相同类型的模式信息 - >脚本表为 - >从SQL Server管理创建到(或更改为)工作室?
I'm looking for a single/flat format that describes the entire table, including constraints, indices, etc.
我正在寻找描述整个表格的单/平面格式,包括约束,索引等。
I am aware of:
我知道:
sp_help table_name
but that doesn't provide the single flat format I'm looking for. Ideally it would be in a scriptable format, such as the AlterTo result that could be executed against the server.
但这并没有提供我正在寻找的单一平面格式。理想情况下,它将采用可编写脚本的格式,例如可以对服务器执行的AlterTo结果。
This is for a scheduled process that documents table schemas on a nightly basis for checking in to version control (SVN).
这是针对计划的进程,该进程每晚记录表模式以检入版本控制(SVN)。
4 个解决方案
#1
Not really. A table def is a collection of columns, constraints etc.
并不是的。表def是列,约束等的集合。
There is an SVN plugin that may help called ScriptDB4SVN. I've not used it personally, I'm going on hearsay.
有一个SVN插件可能有助于称为ScriptDB4SVN。我没有亲自使用它,我正在进行道听途说。
#2
Was searching the 'net again for an answer to this, and came across this SO question. It doesn't accurately capture all the same data as SQL Management Studios Create-to, but enough for my purposes (scripting the database structure for version control purposes).
正在寻找'网再次寻找答案,并遇到了这个问题。它不能准确捕获与SQL Management Studios Create-to相同的所有数据,但足以满足我的目的(为版本控制目的编写数据库结构脚本)。
#3
There is no such command in SQL Server. This is primarily because the Scripting facilitiy is actually in SMO and not in SQL Server itself. There are a number of free console command-line tools that can do it that you could call via xp_CmdShell.
SQL Server中没有这样的命令。这主要是因为Scripting便利实际上是在SMO中而不是在SQL Server本身中。有许多免费的控制台命令行工具可以通过xp_CmdShell调用它。
However, if you really want to do this from T-SQL, then you will need a script or stored procedure that enumerates all of the tables attributes, columns, column datatypes, defaults, nullabilty, etc. etc. and then reassembles it into a CREATE TABLE script. This is a Huge task. That's the bad news. The good news is that someone (Lowell Izaguirre) has already done this and posted it in this article (http://www.sqlservercentral.com/scripts/Miscellaneous/30730/) at SQLServerCentral.Com.
但是,如果您真的想从T-SQL执行此操作,那么您将需要一个脚本或存储过程来枚举所有表属性,列,列数据类型,默认值,nullabilty等等,然后将其重新组合为一个CREATE TABLE脚本。这是一项巨大的任务。这是坏消息。好消息是有人(Lowell Izaguirre)已经这样做了,并在SQLServerCentral.Com上发表了这篇文章(http://www.sqlservercentral.com/scripts/Miscellaneous/30730/)。
Enjoy.
#4
Not really - you can either use C# (or VB.NET) and SMO (SQL Management Objects) to script out your database objects (tables and all), or you can use SQL to get the list of columns for a table:
不是 - 你可以使用C#(或VB.NET)和SMO(SQL管理对象)来编写数据库对象(表和所有)的脚本,或者你可以使用SQL来获取表的列列表:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your Table Name here'
But I don't know of any easy way in SQL itself to create Create/Alter scripts for database objects, sorry.
但是我不知道在SQL本身为数据库对象创建Create / Alter脚本有什么简单的方法,抱歉。
Marc
#1
Not really. A table def is a collection of columns, constraints etc.
并不是的。表def是列,约束等的集合。
There is an SVN plugin that may help called ScriptDB4SVN. I've not used it personally, I'm going on hearsay.
有一个SVN插件可能有助于称为ScriptDB4SVN。我没有亲自使用它,我正在进行道听途说。
#2
Was searching the 'net again for an answer to this, and came across this SO question. It doesn't accurately capture all the same data as SQL Management Studios Create-to, but enough for my purposes (scripting the database structure for version control purposes).
正在寻找'网再次寻找答案,并遇到了这个问题。它不能准确捕获与SQL Management Studios Create-to相同的所有数据,但足以满足我的目的(为版本控制目的编写数据库结构脚本)。
#3
There is no such command in SQL Server. This is primarily because the Scripting facilitiy is actually in SMO and not in SQL Server itself. There are a number of free console command-line tools that can do it that you could call via xp_CmdShell.
SQL Server中没有这样的命令。这主要是因为Scripting便利实际上是在SMO中而不是在SQL Server本身中。有许多免费的控制台命令行工具可以通过xp_CmdShell调用它。
However, if you really want to do this from T-SQL, then you will need a script or stored procedure that enumerates all of the tables attributes, columns, column datatypes, defaults, nullabilty, etc. etc. and then reassembles it into a CREATE TABLE script. This is a Huge task. That's the bad news. The good news is that someone (Lowell Izaguirre) has already done this and posted it in this article (http://www.sqlservercentral.com/scripts/Miscellaneous/30730/) at SQLServerCentral.Com.
但是,如果您真的想从T-SQL执行此操作,那么您将需要一个脚本或存储过程来枚举所有表属性,列,列数据类型,默认值,nullabilty等等,然后将其重新组合为一个CREATE TABLE脚本。这是一项巨大的任务。这是坏消息。好消息是有人(Lowell Izaguirre)已经这样做了,并在SQLServerCentral.Com上发表了这篇文章(http://www.sqlservercentral.com/scripts/Miscellaneous/30730/)。
Enjoy.
#4
Not really - you can either use C# (or VB.NET) and SMO (SQL Management Objects) to script out your database objects (tables and all), or you can use SQL to get the list of columns for a table:
不是 - 你可以使用C#(或VB.NET)和SMO(SQL管理对象)来编写数据库对象(表和所有)的脚本,或者你可以使用SQL来获取表的列列表:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your Table Name here'
But I don't know of any easy way in SQL itself to create Create/Alter scripts for database objects, sorry.
但是我不知道在SQL本身为数据库对象创建Create / Alter脚本有什么简单的方法,抱歉。
Marc