实用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.AlarmRecord
go 
--后删除链接服务器 
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 ***********************