sql server 相关

SELECT IDENT_CURRENT('TableName')+1 --获取表的自增字段值加1

--插入记录后获取当前自增字段值加1
INSERT INTO TableName(XXX) VALUES('XXX'); SELECT @@IDENTITY+1 as ID

--查看各个表的记录数 （不用sql语句）
--企业管理器->右键数据库名->查看->任务板->表信息 可查看各个表的记录数

--附加数据库--
exec sp_attach_db 'TIPS','E:\a_j\backup\TIPS_Data.MDF','E:\a_j\backup\TIPS_Log.LDF';
--添加主键--
use ICS
if not exists (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='tablename')
alter table tablename add primary key (字段名)
GO
if exists (SELECT * from sys.key_constraints where parent_object_id=object_id('tablename') and type='PK')
alter table tablename add primary key (字段名)
GO
--删除表中已存在的主键--
alter table tablename drop constraint 主健名
--查看表的主键名--
exec sp_pkeys tablename
--查看表的外键名--
exec sp_fkeys tablename
--得到表信息。字段，索引。constraint--
exec sp_help tablename

--在订阅服务器上删除残留订阅--
exec sp_subscription_cleanup @publisher = '',@publisher_db = 'TestDB',@publication = 'TestDB';
--此存储过程在发布服务器的任何数据库上执行（用于合并复制）--
sp_mergesubscription_cleanup @publisher = '',@publisher_db = 'TestDB',@publication = 'TestDB';
--在订阅服务器的上除去订阅。在订阅服务器的请求订阅数据库上执行。--
sp_droppullsubscription @publisher = '',@publisher_db = 'TestDB',@publication = '';
--删除订阅--
sp_mergesubscription_cleanup '','','';
--订阅表上的所有订阅--
SELECT * FROM Msreplication_subscriptions;
--在订阅表上删除相关订阅--
DELETE FROM Msreplication_subscriptions where publisher='' and publusher_db='TestDB',publication='TestDB';
--从数据库中删除所有复制对象，而不更新分发服务器上的数据
sp_removedbreplication '<Database name>'
--http://support.microsoft.com/kb/324401/zh-cn

--查看本机客户端IP
use master
exec xp_cmdshell 'ipconfig /all'
--提取本机客户端IP
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetClientIP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetClientIP]
GO
/********************************************************
功能：获取当前客户端IP地址
********************************************************/
/*调用过程：
exec GetClientIP
*/
Create Procedure GetClientIP
as
declare @ip varchar(20)
declare @hst varchar(20)
declare @sql varchar(100)
declare @str varchar(100)
set @str='PING '+Host_Name() + ' -n 1'
create table #tmp(aa varchar(200))
insert #tmp exec master..xp_cmdshell @str
select top 1 @ip = replace(left(aa,charindex(':',aa)-1),'Reply from ','') 
from #tmp where aa like 'reply from %:%'
drop table #tmp
select @ip
return (0)
Go 
EXEC GetClientIP
--查询记录重复--
select * from outrecord where stroutdatetime in(select stroutdatetime from outrecord group by nCardNo,strCardType,strOutDateTime having(count(*)>1));
select nCardNo,strCardType,stroutdatetime,count(*) as countcol from outrecord group by nCardNo,strCardType,strOutDateTime having(count(*)>1)
select nCardNo,strCardType,strInDateTime,count(*) as countcol from outrecord group by nCardNo,strCardType,strInDateTime having(count(*)>1)
select * from otd where stroutdatetime in(select stroutdatetime from otd group by stroutdatetime,ncardno having(count(*)>1))
select strindatetime,stroutdatetime,count(*) as countcol from outrecord group by strindatetime,stroutdatetime having(count(*)>1)
select * from outrecord where strindatetime='2011-06-12 22:09:42' and ncardno=294;
----
--利用临时表，删除完全重复的记录--
if exists(select * from sysobjects where name='tablename' and xtype='U')
if object_id('tempdb..#MyTempTableForCopy') is null
begin
select distinct * into #MyTempTableForCopy from tablename
drop table AdminChargeRecord
select distinct * into tablename from #MyTempTableForCopy
drop table #MyTempTableForCopy
end
;
--创建唯一索引 忽略重复值--
if not exists(select * from sysindexes where id=object_id('tablename') and name='IX_?')
CREATE UNIQUE 
  INDEX [IX_AdminChargeRecord] ON [dbo].[tablename] ([字段1], [字段2])
WITH
    IGNORE_DUP_KEY
ON [PRIMARY];
--临时表是否存在--
if object_id('tempdb..#MyTempTableForCopy') is not null
print 'yy';
--查看索引--
exec sp_helpindex ExchangeRecord
--删除索引--
drop index outrecord.IX_OutRecord
--创建唯一索引 忽略重复值--
if exists(select * from sysindexes where id=object_id('Outrecord') and name='IX_OutRecord')
drop index Outrecord.IX_OutRecord;
CREATE UNIQUE 
  INDEX [IX_OutRecord] ON [dbo].[outrecord] ([nCardNo], [strCardType], [strOutDateTime])
WITH
    IGNORE_DUP_KEY
ON [PRIMARY];
--删除多字段重复记录--
select identity(int,1,1) as autoID,* into #tmp from OutRecord;
delete from #tmp where autoID not in (select min(autoID) as autoID from #tmp group by nCardNo,strCardType,strOutDateTime);
drop table OutRecord;
SELECT nCardNo, strCardType, strCarType, strCarNo, moneyReceivable, moneyReally, 
      moneyBalance, nSeatGroup, strInDateTime, strOutDateTime, strOutPic, nOutAddress, 
      strAdmin, dtWriteDateTime, nChargeForSubPark, strRemark
into OutRecord
FROM #tmp;
drop table #tmp;
--******************************--
--查看当前SQL SERVER的版本信息--
select @@version
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
--存储过程是否存在--
if exists(select * from sysobjects where id = object_id(N'xp_regdeletevalue') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
print 'yyyy'
else
print 'nnnnn';
--表是否已存在--
--方法1 可区分系统表和用户表 xtype='U'/'S'
if not exists(select * from sysobjects where name='defaultValue' and xtype='U')
print 'nnnnn';
--方法2 不区分系统表和用户表
if object_id('systypes') is not null
print 'yy';
--******************************--
--显示当前实例(服务器)名称
select SERVERPROPERTY('servername')
--显示默认实例(服务器)名称
select @@SERVERNAME
--更改服务器名称
if serverproperty('servername') <> @@servername 
begin 
declare @server sysname 
set @server = @@servername 
exec sp_dropserver @server = @server 
set @server = cast(serverproperty('servername') as sysname) 
exec sp_addserver @server = @server , @local = 'LOCAL' 
end

select SERVERPROPERTY('servername'),SERVERPROPERTY('MachineName'),SERVERPROPERTY('InstanceName'),SERVERPROPERTY('Engine Edition')
select left(@@servername,charindex('\',@@servername+'\')-1) 
--
exec sp_help Blacklist
exec sp_helpconstraint 'Blacklist'
--
select b.name from syscolumns a,sysobjects b where a.id=object_id('Blacklist') and b.id=a.cdefault and b.name like 'DF__%__Work%'
--
declare @csname varchar(100)
set @csname=''
select @csname=name FROM sysobjects WHERE xtype='D' AND parent_obj=object_id('Blacklist') and name like 'DF__%__Work%'
exec('alter table Blacklist drop constraint '+ @csname)
--循环插入记录
declare @i as int
set @i=10000;
declare @cn nvarchar(10)
while(@i<16000)
begin
set @cn=convert(nvarchar(10),@i)
insert into #tmpt values(@cn)
set @i=@i+1
end
--导出表数据到TXT文件 如果是默认实例 可以 不指定-S
EXEC master..xp_cmdshell 'bcp "Select ncardno from ips..tablename" queryout c:\tablename.txt -c -S192.168.1.33 -Usa -P'
--从数据库1的表中复制数据到数据库2的表中
insert into ics.dbo.userinfo(strName,strSex,strDepartment,strPhone,strAddress,strRemark) select top 10 strName,strSex,strDepartment,strPhone,strAddress,strRemark from ips.dbo.userinfo

--遍历表相关
USE MASTER 
GO
SP_HELPTEXT sp_MSforeachtable

--下列是 删除 所有表名含有;aonflict 的脚本
DECLARE @Table NVARCHAR(100)  
DECLARE tmpCur CURSOR FOR   
SELECT name FROM dbo.sysobjects WHERE TYPE='U' AND name LIKE N'%aonflict%'  
OPEN tmpCur   
FETCH NEXT FROM tmpCur INTO @Table  
  
WHILE @@FETCH_STATUS = 0   
BEGIN  
    DECLARE @sql VARCHAR(100)  
    SELECT @sql = 'drop table ' + @Table  
    EXEC(@sql)  
    FETCH NEXT FROM tmpCur INTO @Table  
END  
CLOSE tmpCur   
DEALLOCATE tmpCur  


--下列是 删除 所有表名前缀 为;aonflict 的脚本
use ICS
declare mycur cursor local for select [name] from dbo.sysobjects where xtype='U' and left([name],8)='aonflict'
declare @name varchar(100)
OPEN mycur
FETCH NEXT from mycur into @name
WHILE @@FETCH_STATUS = 0 
BEGIN 
exec('drop table ' + @name)
FETCH NEXT from mycur into @name
END 
CLOSE mycur

--删除带默认值的列

declare @name varchar(20)
--backrecord 表
select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('backrecord') and b.id=a.cdefault and a.name='WorkStation' and b.name like 'DF%' 
if(@name <> '')
exec('alter table backrecord drop constraint '+@name)
If exists(select * from syscolumns where id = object_id(N'backrecord') and name = N'WorkStation')
ALTER TABLE backrecord drop column WorkStation
;