1、删除所有表
DECLARE c1 cursor for select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; ' from sysobjects where xtype = 'F' open c1 declare @c1 varchar(8000) fetch next from c1 into @c1 while(@@fetch_status=0) begin exec(@c1) fetch next from c1 into @c1 end close c1 deallocate c1 declare @sql varchar(8000) while (select count(*) from sysobjects where type='U')>0 begin SELECT @sql='drop table ' + name FROM sysobjects WHERE (type = 'U') ORDER BY 'drop table ' + name exec(@sql) end
2、删除所有存储过程
declare @tname varchar(8000) set @tname='' select @tname=@tname + Name + ',' from sysobjects where xtype='P' select @tname='drop proc ' + left(@tname,len(@tname)-1) exec(@tname) go
3、删除所有视图
declare @tname varchar(8000) set @tname='' select @tname=@tname + Name + ',' from sysobjects where xtype='V' select @tname='drop view ' + left(@tname,len(@tname)-1) exec(@tname) go
4、递归查询 使用关键字with as
with temp ( [Id], [parentid]) as ( select Id, ParentId from SysLocation where ParentId = @ParentId union all select a.Id, a.ParentId from SysLocation a inner join temp on a.ParentId = temp.[Id] ) select s.Id, s.ParentId from SysLocation s where Id=@ParentId union all select * from temp
5、数字类型转为字符串
select convert(varchar(11),convert(decimal(11,0),mo)) as m from test
6、创建外键
alter table ResolvedOrderNew add constraint FK_ResolvedOrderNew_QuotaOrderNew foreign key (QuoteOrderNewId) references QuoteOrderNew(Id);
转载于:https://www.cnblogs.com/zoro-zero/p/10734651.html
