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

--查看當(dāng)前數(shù)據(jù)庫有哪些連接

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

------------------------遠(yuǎn)程服務(wù)器數(shù)據(jù)庫操作-----------------------------------創(chuàng)建鏈接服務(wù)器------------- 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' --這個(gè)允許調(diào)用鏈接服務(wù)器上的存儲(chǔ)過程 go--查詢示例 select Count(*) from srv_lnk.GviewsDB.dbo.AlarmRecord--導(dǎo)入示例 select * into 表 from srv_lnk.數(shù)據(jù)庫名.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 --后刪除鏈接服務(wù)器 exec sp_dropserver 'srv_lnk','droplogins'------------------------遠(yuǎn)程服務(wù)器數(shù)據(jù)庫操作------------------------

--------常用系統(tǒng)存儲(chǔ)過程有：exec sp_databases; --查看數(shù)據(jù)庫exec sp_tables;        --查看表exec sp_columns student;--查看列exec sp_helpIndex userinfo;--查看索引exec sp_helpConstraint student;--約束exec sp_stored_procedures;exec sp_helptext 'sp_browsereplcmds';--查看存儲(chǔ)過程創(chuàng)建、定義語句exec sp_rename student, stuInfo;--修改表、索引、列的名稱exec sp_renamedb myTempDB, myDB;--更改數(shù)據(jù)庫名稱exec sp_defaultdb 'master', 'myDB';--更改登錄名的默認(rèn)數(shù)據(jù)庫exec sp_helpdb master;--數(shù)據(jù)庫幫助，查詢數(shù)據(jù)庫信息

--查詢所有存儲(chǔ)過程

select * from sys.objects where type = 'P';select * from sys.objects where type_desc like '%pro%' and name like 'sp%';--查詢所有觸發(fā)器select * from sys.objects where type = 'TR' order by name;--查詢所有索引select * from sys.indexes where name like 'IX%' order by name;

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