---2005附加數據庫
---ATTACH DATABASE TEMPLATE
---涂聚文 2012 元旦
exec sp_attach_db 'Asset5','D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Asset5.mdf','D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Asset5_log.ldf'
GO
---列出存儲過程
exec sp_stored_procedures
GO
--系統視圖
select * from sys.objects
---列出存儲過程
select * from sys.objects WHERE TYPE='P'
select [name] from sysobjects where xtype='P' order by [name]
GO
---列出所有表
select * from sys.objects WHERE TYPE='U' order by [name]
select [name] from sysobjects where xtype='U' order by [name]
GO
--列出視圖
select * from sys.objects WHERE TYPE='V' order by [name]
select [name] from sysobjects where xtype='V' order by [name]
GO
--
select * from sysobjects
GO
--列出所有表
select [name] from sysobjects where xtype='u' order by [name]
GO
--查詢數據庫中的表所占用空間
exec sp_spaceused '表名' --取得表占用空間
exec sp_spaceused ''--數據庫所有空間
---1 種方式
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))
GO
declare @name varchar(100)
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
insert into #data
exec sp_spaceused @name
print @name
fetch next from cur into @name
end
close cur
deallocate cur
go
select * from #Data
GO
---2 種方式
create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int)
GO
insert into #dataNew
select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data,
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data
GO
select * from #dataNew order by data desc
GO
---數據庫對象限定符:
--[[[server.][database].][schema].]database_object
---schema dbo(默認模式)
/*
使用完毕后，记得一定要要关闭它，因为这是一个安全隐患,切记执行下面的SQL语句
--创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
--查询示例
select * from ITSV.数据库名.dbo.表名
--导入示例
select * into 表 from ITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器
exec sp_dropserver 'ITSV ', 'droplogins '
--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset
--查询示例
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--把本地表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表
--更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1
--openquery用法需要创建一个连接
--首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把本地表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A
--3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
select * from 本地表
*/
--2005 启用Ad Hoc Distributed Queries的方法，执行下面的查询语句就可以了：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--使用完毕后，记得一定要要关闭它，因为这是一个安全隐患,切记执行下面的SQL语句
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
--2005
SELECT top 10 * FROM OPENDATASOURCE('SQLOLEDB','Data Source=.;User ID=sa;Password=geovindu;').geovinduDB.dbo.meetingApply
GO
--openrowset使用OLEDB的一些例子
select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;','select * from GEOVINDU.dbo.school') as t
select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;',GEOVINDU.dbo.school) as t
select * from openrowset('SQLOLEDB','Server=(local);Trusted_Connection=yes;',GEOVINDU.dbo.school) as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***','select * from GEOVINDU.dbo.school') as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***',GEOVINDU.dbo.school) as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***','select school.id as id1,people.id as id2 from GEOVINDU.dbo.school inner join GEOVINDU.dbo.people on school.id=people.id') as t
--openrowset使用SQLNCLI的一些例子(SQLNCLI在SqlServer2005以上才能使用)
select * from openrowset('SQLNCLI','(local)';'sa';'***','select * from GEOVINDU.dbo.school') as t
select * from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select * from GEOVINDU.dbo.school') as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;','select * from GEOVINDU.dbo.school') as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;',GEOVINDU.dbo.school) as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=GEOVINDU','select * from dbo.school') as t
--openrowset其他使用
insert openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from GEOVINDU.dbo.school where id=1') values('geovindu')/*要不要where都一样，插入一行*/
update openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from GEOVINDU.dbo.school where id=1') set name='geovindu'
delete from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from GEOVINDU.dbo.school where id=1')
--opendatasource使用SQLNCLI的一些例子
select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;').GEOVINDU.dbo.school as t
select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=TB').GEOVINDU.dbo.school as t
--opendatasource使用OLEDB的例子
select * from opendatasource('SQLOLEDB','Server=(local);Trusted_Connection=yes;').GEOVINDU.dbo.school as t
--opendatasource其他使用
insert opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').GEOVINDU.dbo.school(name) values('geovindu')/*要不要where都一样，插入一行*/
update opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').GEOVINDU.dbo.school set name='geovindu'
delete from opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').GEOVINDU.dbo.school where id=1
--openquery使用OLEDB的一些例子
exec sp_addlinkedserver 'ITSV', '', 'SQLOLEDB','(local)'
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', '***'
select * FROM openquery(ITSV, 'SELECT * FROM GEOVINDU.dbo.school ')
--openquery使用SQLNCLI的一些例子
exec sp_addlinkedserver 'ITSVA', '', 'SQLNCLI','(local)'
exec sp_addlinkedsrvlogin 'ITSVA', 'false',null, 'sa', '***'
select * FROM openquery(ITSVA, 'SELECT * FROM GEOVINDU.dbo.school ')
--openquery其他使用
insert openquery(ITSVA,'select name from GEOVINDU.dbo.school where id=1') values('geovindu')/*要不要where都一样，插入一行*/
update openquery(ITSVA,'select name from GEOVINDU.dbo.school where id=1') set name='geovindu'
delete openquery(ITSVA,'select name from GEOVINDU.dbo.school where id=1')
backup database intranet to disk='C:\ba.bak' with init
RESTORE DATABASE nwind_new FROM DISK = 'c:\backups\northwind\nwind.bak'
WITH
MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL
Server\Data\nwind_new.mdf'
MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL
Server\Data\nwind_new_log.ldf'
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test.xls;Extended Properties=EXCEL 5.0'
)...[Sheet1$];
SELECT * FROM
OpenDataSource(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\temp\payroll.mdb";
User ID=Admin;Password=;')...employees
SELECT *
FROM OPENROWSET
( BULK 'C:\data.txt',SINGLE_CLOB)
AS a
