常用sql语句（清空表，当前哭下的存储过程，删除所有存储过程，添加查询记录编号）

[sql]代码库

--清空表
declare @trun_name varchar(8000)
declare name_cursor cursor for
select 'delete  from ' + name + ' DBCC CHECKIDENT ('+name+', RESEED,0)' from [sysobjects] where xtype='U'
open name_cursor
fetch next from name_cursor into @trun_name
while @@FETCH_STATUS = 0
begin
 print @trun_name
 fetch next from name_cursor into @trun_name
end
close name_cursor
deallocate name_cursor

--exec sp_msforeachtable "truncate table ?"

--查询当前数据库下的所有存储过程

select 
  pro.name  AS  ProcedureName,
  c.text AS CreateProcedureSQL
from 
  sys.procedures pro LEFT OUTER JOIN
     dbo.syscomments c ON pro.object_id = c.id
     
     
--删除当前数据库下的所有用户存储过程

DECLARE @STRING VARCHAR(8000)
WHILE EXISTS(SELECT NAME FROM SYSOBJECTS WHERE TYPE='P' AND STATUS>=0)BEGIN
SELECT @STRING='DROP PROCEDURE '+NAME FROM SYSOBJECTS WHERE TYPE = 'P' AND STATUS>=0
SELECT @STRING
EXEC(@STRING) END


--为查询结果自动生成记录编号

--select  ROW_NUMBER() OVER (ORDER BY 排序列 [ASC|DESC]) as 编号 from 表名