删除某个架构下所有表的主外键, 然后删除该架构下所有的表

select 
  'ALTER TABLE [lcms].['+o.name+'] drop CONSTRAINT '+fk.name+';'  AS  Command
from 
  sys.foreign_keys  fk  
  JOIN sys.all_objects  o ON (fk.parent_object_id=o.object_id)
 where o.schema_id=8

得到架构id:

SELECT s.schema_id
FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id 
WHERE s.Name = 'lcms'

删除某一个架构下所有的表

select 
  'drop TABLE [lcms].[' + t.name + ']'  AS Command
from  sys.schemas as s left join sys.tables as t on s.schema_id=t.schema_id 
 
where s.schema_id=8
  

生成删除某一个架构下所有的表的关联关系的sql语句

select
 'ALTER TABLE [lcms].['+o.name+'] drop CONSTRAINT '+fk.name+';' 
from
 sys.foreign_keys fk 
 JOIN sys.all_objects o ON (fk.parent_object_id=o.object_id)
where o.schema_id in (
select s.schema_id from sys.schemas as s
where s.name='架构名'
)

批量生产一些sql语句, 比如批量删除修改字段名的sql语句;

select  
 ' alter table SES.'+t.name+' add CreateDate datetime not null; '
 
from  sys.schemas as s left join sys.tables as t on s.schema_id=t.schema_id   
   
where s.schema_id=10

select  
 ' EXECUTE sp_rename N''SES.'+t.name+'.UpdatedBy'',N''LastUpdatedByUserID'', ''COLUMN'';'   
from  sys.schemas as s left join sys.tables as t on s.schema_id=t.schema_id   
   
where s.schema_id=10

select  
 ' alter table SES.'+t.name+' drop column CreatedByUserID ; '
 
from  sys.schemas as s left join sys.tables as t on s.schema_id=t.schema_id   
   
where s.schema_id=10