如何使用查询为现有表生成 SQL 创建脚本
此问题涉及为已存在的表生成 SQL CREATE 脚本。虽然提供的语法准确地解决了此任务,但可以对响应进行一些改进。
改进的响应:
这可能对您有帮助。该脚本生成索引、外键 (FK)、主键 (PK) 以及任何表的通用结构。该脚本由一个查询部分组成,该部分收集表信息并连接适当的 CREATE 语句。
例如,考虑以下表定义:
CREATE TABLE [dbo].[WorkOut] ( [WorkOutID] [bigint] IDENTITY(1,1) NOT NULL, [TimeSheetDate] [datetime] NOT NULL, [DateOut] [datetime] NOT NULL, [EmployeeID] [int] NOT NULL, [IsMainWorkPlace] [bit] NOT NULL, [DepartmentUID] [uniqueidentifier] NOT NULL, [WorkPlaceUID] [uniqueidentifier] NULL, [TeamUID] [uniqueidentifier] NULL, [WorkShiftCD] [nvarchar](10) NULL, [WorkHours] [real] NULL, [AbsenceCode] [varchar](25) NULL, [PaymentType] [char](2) NULL, [CategoryID] [int] NULL, [Year] AS (datepart(year,[TimeSheetDate])), CONSTRAINT [PK_WorkOut] PRIMARY KEY CLUSTERED ([WorkOutID] ASC) ) ON [PRIMARY] ALTER TABLE [dbo].[WorkOut] ADD CONSTRAINT [DF__WorkOut__IsMainW__2C1E8537] DEFAULT ((1)) FOR [IsMainWorkPlace] ALTER TABLE [dbo].[WorkOut] WITH CHECK ADD CONSTRAINT [FK_WorkOut_Employee_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID]) ALTER TABLE [dbo].[WorkOut] CHECK CONSTRAINT [FK_WorkOut_Employee_EmployeeID]
查询脚本可用于生成用于重新创建表的 DDL 语句。
Declare @table_name SYSNAME = 'dbo.WorkOut' Declare @object_name SYSNAME, @object_id INT SELECT @object_name = '[' + s.name + '].[' + o.name + ']', @object_id = o.[object_id] FROM sys.objects o WITH (NOWAIT) JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] WHERE s.name + '.' + o.name = @table_name AND o.[type] = 'U' AND o.is_ms_shipped = 0 DECLARE @SQL NVARCHAR(MAX) = '' ;WITH index_column AS ( SELECT ic.[object_id], ic.index_id, ic.is_descending_key, ic.is_included_column, c.name FROM sys.index_columns ic WITH (NOWAIT) JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id WHERE ic.[object_id] = @object_id ), fk_columns AS ( SELECT k.constraint_object_id, cname = c.name, rcname = rc.name FROM sys.foreign_key_columns k WITH (NOWAIT) JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id WHERE k.parent_object_id = @object_id ) SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF(( SELECT CHAR(9) + ', [' + c.name + '] ' + CASE WHEN c.is_computed = 1 THEN 'AS ' + cc.[definition] ELSE UPPER(tp.name) + CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')' WHEN tp.name IN ('nvarchar', 'nchar', 'ntext') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')' WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')' WHEN tp.name IN ('decimal', 'numeric') THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')' ELSE '' END + CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END + CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END + CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END END + CHAR(13) FROM sys.columns c WITH (NOWAIT) JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id WHERE c.[object_id] = @object_id ORDER BY c.column_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ') + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' + (SELECT STUFF(( SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END FROM sys.index_columns ic WITH (NOWAIT) JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id WHERE ic.is_included_column = 0 AND ic.[object_id] = k.parent_object_id AND ic.index_id = k.unique_index_id FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) + ')' + CHAR(13) FROM sys.key_constraints k WITH (NOWAIT) WHERE k.parent_object_id = @object_id AND k.[type] = 'PK'), '') + ')' + CHAR(13) + ISNULL((SELECT ( SELECT CHAR(13) + 'ALTER TABLE ' + @object_name + ' WITH' + CASE WHEN fk.is_not_trusted = 1 THEN ' NOCHECK' ELSE ' CHECK' END + ' ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY(' + STUFF(( SELECT ', [' + k.cname + ']' FROM fk_columns k WHERE k.constraint_object_id = fk.[object_id] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] (' + STUFF(( SELECT ', [' + k.rcname + ']' FROM fk_columns k WHERE k.constraint_object_id = fk.[object_id] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + CASE WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL' WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' ELSE '' END + CASE
以上是如何为现有表生成 SQL CREATE 脚本?的详细内容。更多信息请关注PHP中文网其他相关文章!

MySQL通过异步、半同步和组复制三种模式处理数据复制。1)异步复制性能高但可能丢失数据。2)半同步复制提高数据安全性但增加延迟。3)组复制支持多主复制和故障转移,适用于高可用性需求。

EXPLAIN语句可用于分析和提升SQL查询性能。1.执行EXPLAIN语句查看查询计划。2.分析输出结果,关注访问类型、索引使用情况和JOIN顺序。3.根据分析结果,创建或调整索引,优化JOIN操作,避免全表扫描,以提升查询效率。

使用mysqldump进行逻辑备份和MySQLEnterpriseBackup进行热备份是备份MySQL数据库的有效方法。1.使用mysqldump备份数据库:mysqldump-uroot-pmydatabase>mydatabase_backup.sql。2.使用MySQLEnterpriseBackup进行热备份:mysqlbackup--user=root--password=password--backup-dir=/path/to/backupbackup。恢复时,使用相应的命

MySQL慢查询的主要原因包括索引缺失或不当使用、查询复杂度、数据量过大和硬件资源不足。优化建议包括:1.创建合适的索引;2.优化查询语句;3.使用分表分区技术;4.适当升级硬件。

MySQL视图是基于SQL查询结果的虚拟表,不存储数据。1)视图简化复杂查询,2)增强数据安全性,3)维护数据一致性。视图是数据库中的存储查询,可像表一样使用,但数据动态生成。

mysqldiffersfromothersqldialectsinsyntaxforlimit,自动启动,弦乐范围,子征服和表面上分析。1)MySqluessLipslimit,whilesqlserverusestopopandoraclesrontersrontsrontsrontsronnum.2)

MySQL分区能提升性能和简化维护。1)通过按特定标准(如日期范围)将大表分成小块,2)物理上将数据分成独立文件,3)查询时MySQL可专注于相关分区,4)查询优化器可跳过不相关分区,5)选择合适的分区策略并定期维护是关键。

在MySQL中,如何授予和撤销权限?1.使用GRANT语句授予权限,如GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host';2.使用REVOKE语句撤销权限,如REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host',确保及时沟通权限变更。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

热工具

螳螂BT
Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

mPDF
mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),

Atom编辑器mac版下载
最流行的的开源编辑器

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)