【sql】sql技巧

--修改字段名称
EXEC sp_rename 'Agent_Auth_Table.[DptID2]', 'DptID', 'COLUMN'

--修改字段类型
ALTER TABLE Agent_Auth_Table
ALTER COLUMN DptID int

--增加字段
alter table Agent_Auth_Table add AddTime

--显示存储过程的内容
sp_helptext   '存储过程名称 ' 

--EXEC FindNewAddRsTable_A
--EXEC FindNewAddRsTable_B
--DROP PROCEDURE FindNewAddRsTable_A
--DROP PROCEDURE FindNewAddRsTable_B
--
--查找数据库中增加了记录的表

CREATE PROCEDURE FindNewAddRsTable_A
AS 
-----------------------第一步，未添加----------------------------------------------------------------------------
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#t1') and type='U')drop table #t1    
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#t2') and type='U')drop table #t2    

select * into #t1 from sysobjects where xtype='U' and status>=0 order by name,crdate

select * into #t2 from sysindexes where id in(select id from sysobjects where xtype='U' and status>=0) and indid in (0,1) 


if exists (select * from dbo.sysobjects where id = object_id('test1') and type='U')drop table test1 
select #t1.id,#t1.name,#t2.rows INTO test1 from #t1,#t2 where #t1.id=#t2.id  order by rows
-----------------------------------------------------------------------------------------------SELECT * FROM test1
go

CREATE PROCEDURE FindNewAddRsTable_B
AS 

----------------------第二步，已添加，对比-----------------------------------------------------------------------------
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#t1') and type='U')drop table #t1    
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#t2') and type='U')drop table #t2    

select * into #t1 from sysobjects where xtype='U' and status>=0 order by name,crdate
select * into #t2 from sysindexes where id in(select id from sysobjects where xtype='U' and status>=0) and indid in (0,1) 

if exists (select * from dbo.sysobjects where id = object_id('test2') and type='U')drop table test2 
select #t1.id,#t1.name,#t2.rows INTO test2 from #t1,#t2 where #t1.id=#t2.id  order by #t1.name
-----------------------------------------------------------------------------------------------------SELECT * FROM 

--第三步 对比 建立新表，记录新增记录的表名
if NOT exists (select * from dbo.sysobjects where id = object_id('test3') and type='U')   CREATE TABLE test3(    TableName VARCHAR(50),    AddTime DATETIME DEFAULT GETDATE())
INSERT INTO test3 SELECT test1.NAME,GETDATE() FROM test1,test2 WHERE test1.id=test2.id AND test1.ROWS<>test2.ROWS
SELECT * FROM test3 ORDER BY AddTime DESC 
---------------------------------------------------------------------------------------------

SET IDENTITY_INSERT [TABLE] [ON|OFF]--开关自动增长
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE  --使用bcp要启用cmdshell