将数据库所有表转为执行脚本

select t.category, t.type, t.xtype from sys.sysobjects t group by t.category, t.type, t.xtype;
/*
category	type	xtype
0	F 	F  foreign key
0	FN	FN Function
0	K 	PK primary key
0	K 	UQ 
0	P 	P  stored procedures
0	SN	SN synonyms
0	TF	TF
0	TR	TR trigger
0	U 	U  tables
0	V 	V  views
2	IT	IT 
2	S 	S 
2	SQ	SQ
2	TT	TT	type
2048	D 	D 
*/


--truncate table T_TRN_SMS_ARCH;

--insert into SmartSer_CN_TEST.dbo.T_TRN_SMS_ARCH
--select top 1 * from SmartSer_CN_INT.dbo.T_TRN_SMS_ARCH order by SMS_SENTDATE desc;

select  t.* from sys.syscolumns t where t.id=1481772336 order by colorder;

if exists(select 1 from sys.sysobjects t where t.name='FK1_T_TRN_APPLICATION_CUSTOMER')
begin
	--alter table T_TRN_APPLICATION_CUSTOMER drop constraint FK1_T_TRN_APPLICATION_CUSTOMER
	select 111;
end

--获取所有有主键的表信息
select u.name, t.* from sys.sysobjects t 
left join sys.sysobjects u on t.parent_obj=u.id
where t.type='K' and t.xtype='PK'
order by t.type

select  stuff((select ', ' + t.name from sys.syscolumns t where t.id=1481772336 for xml path('')), 1, 2, 'select ') 
from sys.syscolumns t where t.id=1481772336 group by t.id;

--表和字段
select t.id, t.name, u.name from sys.sysobjects t 
left join sys.syscolumns u on t.id=u.id
where t.category=0 and t.type='U' 
	and t.name not in ('GenerateOTPTestData', 'sysdiagrams', 'T_TRN_CONTRACT_copy1') 
	and t.name not like '%_bak%' and t.name not like '%_afc%' and t.name not like '%_hil%'
order by t.name;

--获取所有有主键的表信息
select t.id, t.name, u.name 
from sys.sysobjects t 
left join sys.sysobjects u on t.id=u.parent_obj and u.type='K' and u.xtype='PK'
--left join sys.syscolumns u on t.id=u.id
where t.category=0 and t.type='U' 
	and t.name not in ('GenerateOTPTestData', 'sysdiagrams', 'T_TRN_CONTRACT_copy1') 
	and t.name not like '%_bak%' and t.name not like '%_afc%' and t.name not like '%_hil%'
order by t.name;

--根据表名和字段名拼成sql脚本
select t.id, t.name, u.name,
	'if exists(select 1 from sys.sysobjects t where t.name='''+ t.name +''') begin  alter table ' + u.name + ' drop constraint ' + t.name + '  end;' as SqlScripts
from sys.sysobjects t 
left join sys.sysobjects u on t.parent_obj=u.id
--left join sys.syscolumns u on t.id=u.id
where t.category=0 and (t.type='F' or t.type='K')
	and t.name not in ('GenerateOTPTestData', 'sysdiagrams', 'T_TRN_CONTRACT_copy1') 
	and t.name not like '%_bak%' and t.name not like '%_afc%' and t.name not like '%_hil%'
order by t.name;


select t.id, t.name, --u.name,
	'truncate table SmartSer_CN_TEST.dbo.' + t.name + ';' as SqlScripts
from sys.sysobjects t 
left join sys.syscolumns u on t.id=u.id
left join #orderTable p on t.id=p.TableID
where t.category=0 and t.type='U' 
	and t.name not in ('GenerateOTPTestData', 'sysdiagrams', 'T_TRN_CONTRACT_copy1') 
	and t.name not like '%_bak%' and t.name not like '%_afc%' and t.name not like '%_hil%'
group by t.id, t.name, p.orderNumber
order by p.orderNumber desc, t.id desc;



WITH TablesCTE(SchemaName, TableName, TableID, Ordinal) AS
(
    SELECT OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        0 AS Ordinal
    FROM sys.objects AS so
    WHERE so.type = 'U' AND so.is_ms_Shipped = 0
    UNION ALL
    SELECT OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        tt.Ordinal + 1 AS Ordinal
    FROM sys.objects AS so
    INNER JOIN sys.foreign_keys AS f ON f.parent_object_id = so.object_id AND f.parent_object_id != f.referenced_object_id
    INNER JOIN TablesCTE AS tt ON f.referenced_object_id = tt.TableID
    WHERE so.type = 'U' AND so.is_ms_Shipped = 0
)
SELECT DISTINCT RANK() over(order by t.Ordinal, t.TableName) orderNumber,t.Ordinal, t.TableName, t.TableID 
into #orderTable
FROM TablesCTE AS t
INNER JOIN (SELECT
		itt.SchemaName as SchemaName, 
		itt.TableName as TableName,
		itt.TableID as TableID,
		Max(itt.Ordinal) as Ordinal
	FROM TablesCTE AS itt
	GROUP BY itt.SchemaName, itt.TableName, itt.TableID ) AS tt ON t.TableID = tt.TableID AND t.Ordinal = tt.Ordinal
ORDER BY t.Ordinal, t.TableName




select t.id, t.name, --u.name,
	(case when q.name is not null then 'set identity_insert SmartSer_CN_TEST.dbo.' + t.name + ' on;' else '' end) +
	stuff((select ', ' + k.name from sys.syscolumns k where k.id=t.id order by colorder for xml path('')), 1, 2, ('insert into SmartSer_CN_TEST.dbo.' + t.name + ' (')) + ') ' +
	stuff((select ', ' + k.name from sys.syscolumns k where k.id=t.id order by colorder for xml path('')), 1, 2, 'select ') + ' from SmartSer_CN_INT.dbo.' + t.name + ';' +
	(case when q.name is not null then 'set identity_insert SmartSer_CN_TEST.dbo.' + t.name + ' off;' else '' end) as SqlScripts
from sys.sysobjects t 
left join sys.syscolumns u on t.id=u.id
left join #orderTable p on t.id=p.TableID
left join sys.sysobjects q on t.id=q.parent_obj and q.type='K' and q.xtype='PK'
where t.category=0 and t.type='U' 
	and t.name not in ('GenerateOTPTestData', 'sysdiagrams', 'T_TRN_CONTRACT_copy1') 
	and t.name not like '%_bak%' and t.name not like '%_afc%' and t.name not like '%_hil%'
group by t.id, t.name, p.orderNumber, q.name
order by p.orderNumber asc, t.id asc;



-- For Test.
begin
begin try
	begin tran

	truncate table T_MST_EMAIL_NOTICE;
	truncate table T_MST_EMAIL;
	
	set identity_insert T_MST_EMAIL on;
	insert into SmartSer_CN_TEST.dbo.T_MST_EMAIL (EMAIL_ID, GROUP_ID, TITLE, CONTENT, REMARK, STATUS, CREATE_DATE, UPDATE_DATE) select EMAIL_ID, GROUP_ID, TITLE, CONTENT, REMARK, STATUS, CREATE_DATE, UPDATE_DATE from SmartSer_CN_INT.dbo.T_MST_EMAIL;
	set identity_insert T_MST_EMAIL off;
	set identity_insert T_MST_EMAIL_NOTICE on;
	insert into SmartSer_CN_TEST.dbo.T_MST_EMAIL_NOTICE (ID, ENTITY_CODE, EMAIL_ID, EMAIL_TITLE, EMAIL_CONTENT, ATTACHMENT_FILE, SOURCE_ID, REMARK, SEND_FROM, SEND_TO, SEND_CC, IS_SEND, SEND_DATE, MESSAGE, STATUS, CREATE_DATE, UPDATE_DATE) select ID, ENTITY_CODE, EMAIL_ID, EMAIL_TITLE, EMAIL_CONTENT, ATTACHMENT_FILE, SOURCE_ID, REMARK, SEND_FROM, SEND_TO, SEND_CC, IS_SEND, SEND_DATE, MESSAGE, STATUS, CREATE_DATE, UPDATE_DATE from SmartSer_CN_INT.dbo.T_MST_EMAIL_NOTICE;
	set identity_insert T_MST_EMAIL_NOTICE off;
	

	commit tran;
end try
begin catch
	rollback;

	SELECT ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE();
end catch
end


--IDENTITY_INSERT is already ON for table 'SmartSer_CN_TEST.dbo.T_TRN_CONTRACT'. Cannot perform SET operation for table 'T_MST_EMAIL'.




go
----T_MST_ACTIVITY_DETAIL --额外处理
--if exists (select * from sys.objects where object_id = object_id('T_MST_ACTIVITY_DETAIL'))
--begin
--	drop table T_MST_ACTIVITY_DETAIL
--end
--else
--begin

--	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_MST_ACTIVITY_DETAIL]') AND type in (N'U'))
--	BEGIN
--	CREATE TABLE [dbo].[T_MST_ACTIVITY_DETAIL](
--		[DETAIL_ID] [int] IDENTITY(1,1) NOT NULL,
--		[CONTRACT_NO] [nvarchar](50) NULL,
--		[ACTIVITY_ID] [int] NULL,
--		[STATUS] [int] NULL,
--		[IS_LIKE] [int] NULL,
--	 CONSTRAINT [PK_T_MST_ACTIVITY_LOG] PRIMARY KEY CLUSTERED 
--	(
--		[DETAIL_ID] ASC
--	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MLP_PRDData01]
--	) ON [MLP_PRDData01]
--	END

--	set identity_insert SmartSer_CN_TEST.dbo.T_MST_ACTIVITY_DETAIL on;
--	insert into SmartSer_CN_TEST.dbo.T_MST_ACTIVITY_DETAIL (DETAIL_ID, CONTRACT_NO, ACTIVITY_ID, STATUS, IS_LIKE) select DETAIL_ID, CONTRACT_NO, ACTIVITY_ID, STATUS, IS_LIKE from SmartSer_CN_INT.dbo.T_MST_ACTIVITY_DETAIL;
--	set identity_insert SmartSer_CN_TEST.dbo.T_MST_ACTIVITY_DETAIL off;

--end



--添加Drop表后的外键约束、默认值等

ALTER TABLE [dbo].[T_TRN_CONTRACT_CUSTOMER]  WITH CHECK ADD  CONSTRAINT [FK1_T_TRN_CONTRACT_CUSTOMER] FOREIGN KEY([INT_CONTRACT_INTERNAL_ID])
REFERENCES [dbo].[T_TRN_CONTRACT] ([INT_CONTRACT_INTERNAL_ID])
GO

ALTER TABLE [dbo].[T_TRN_CONTRACT_CUSTOMER] CHECK CONSTRAINT [FK1_T_TRN_CONTRACT_CUSTOMER]
GO



--set identity_insert T_TRN_APPLICATION_LANDING_PAGE_URL_MAPPING off;

--没有权限，木的办法
--sp_configure   'allow   updates ',   1
--GO
--reconfigure   with   override
--GO
--update   syscolumns   set   colstat   =   0
--where     id=object_id( 'T_MST_ACTIVITY_DETAIL ')   and   name= 'DETAIL_ID'
--GO
--sp_configure   'allow   updates ',   0
go


select * from sys.syscolumns t where t.id=object_id('T_MST_ACTIVITY_DETAIL') and t.name='DETAIL_ID'