实用SQL语句

****************** sql server x ***********************

--查看当前数据库有哪些连接

SELECT * FROM [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN (  SELECT    [DBID]  FROM    [Master].[dbo].[SYSDATABASES]   WHERE    NAME='YourDBName')

------------------------远程服务器数据库操作-----------------------------------创建链接服务器------------- exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','211.154.144.68,2013' exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'_DB_ADMIN','' exec sp_serveroption 'srv_lnk','rpc out','true' --这个允许调用链接服务器上的存储过程 go--查询示例 select Count(*) from srv_lnk.GviewsDB.dbo.AlarmRecord--导入示例 select * into 表 from srv_lnk.数据库名.dbo.表名insert into dbo.AlarmRecord(iMsgID,iSrcUserID,iDesUserID,iType,iArea,iChannel,iphone,strEmail,strImage,iStatus,dtCreateTime,dtReceiveTime) select iMsgID,iSrcUserID,iDesUserID,iType,iArea,iChannel,iPhone,strEmail,strImage,iStatus,dtCreateTime,dtReceiveTime from srv_lnk.GviewsDB.dbo.AlarmRecordgo --后删除链接服务器 exec sp_dropserver 'srv_lnk','droplogins'------------------------远程服务器数据库操作------------------------

--------常用系统存储过程有：exec sp_databases; --查看数据库exec sp_tables;        --查看表exec sp_columns student;--查看列exec sp_helpIndex userinfo;--查看索引exec sp_helpConstraint student;--约束exec sp_stored_procedures;exec sp_helptext 'sp_browsereplcmds';--查看存储过程创建、定义语句exec sp_rename student, stuInfo;--修改表、索引、列的名称exec sp_renamedb myTempDB, myDB;--更改数据库名称exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库exec sp_helpdb master;--数据库帮助，查询数据库信息

--查询所有存储过程

select * from sys.objects where type = 'P';select * from sys.objects where type_desc like '%pro%' and name like 'sp%';--查询所有触发器select * from sys.objects where type = 'TR' order by name;--查询所有索引select * from sys.indexes where name like 'IX%' order by name;

****************** sql server x ***********************