I want to generate create table script of already created table in my database.
I know we can generate scripts using right click on table and click on 'script table as' menu and script will be generated. Because I want to execute this by using SSIS package so I need a generated script file of modified database.
I want to do same process using SQL query. is there any way??
Try this one -
DECLARE @object_id INT, @object_name SYSNAME DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT o.[object_id], '[' + s.name + '].[' + o.name + ']' FROM sys.objects o WITH (NOWAIT) JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U' AND o.is_ms_shipped = 0 OPEN cur FETCH NEXT FROM cur INTO @object_id, @object_name WHILE @@FETCH_STATUS = 0 BEGIN 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 = 'decimal' THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')' 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 WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE' WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL' WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT' ELSE '' END + CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name + ']' + CHAR(13) FROM sys.foreign_keys fk WITH (NOWAIT) JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id WHERE fk.parent_object_id = @object_id FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '') + ISNULL(((SELECT CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END + ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' + STUFF(( SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END FROM index_column c WHERE c.is_included_column = 0 AND c.index_id = i.index_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' + ISNULL(CHAR(13) + 'INCLUDE (' + STUFF(( SELECT ', [' + c.name + ']' FROM index_column c WHERE c.is_included_column = 1 AND c.index_id = i.index_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13) FROM sys.indexes i WITH (NOWAIT) WHERE i.[object_id] = @object_id AND i.is_primary_key = 0 AND i.[type] = 2 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ), '') + CHAR(13) + 'GO' PRINT @SQL FETCH NEXT FROM cur INTO @object_id, @object_name END CLOSE cur DEALLOCATE cur Output -
CREATE TABLE [dbo].[test1] ( [ID] INT NOT NULL , CONSTRAINT [PK_test1_ID] PRIMARY KEY ([ID] ASC) ) GO CREATE TABLE [dbo].[tbl_name] ( [ID] INT NOT NULL , CONSTRAINT [PK_tbl_name_ID] PRIMARY KEY ([ID] ASC) ) GO You can try like this one:
declare @vsSQL varchar(8000) declare @vsTableName varchar(50) select @vsTableName = '<TABLENAME>' select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10) select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' + st.Name + case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end + case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10) from sysobjects so join syscolumns sc on sc.id = so.id join systypes st on st.xusertype = sc.xusertype where so.name = @vsTableName order by sc.ColID select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')' 
