---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_proceduresGO--系統視圖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 sysobjectsGO--列出所有表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))GOdeclare @name varchar(100)declare cur cursor for select name from sysobjects where xtype='u' order by nameopen curfetch next from cur into @namewhile @@fetch_status=0begin insert into #data exec sp_spaceused @name print @name fetch next from cur into @nameendclose curdeallocate curgoselect * from #DataGO---2 種方式create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int)GOinsert into #dataNewselect 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 #dataGOselect * from #dataNew order by data descGO---數據庫對象限定符:--[[[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 bset b.列A=a.列Afrom openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 bon 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 bset b.列B=a.列BFROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a inner join 本地表 b on a.列A=b.列A--3、opendatasource/openrowsetSELECT *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',1reconfigureexec sp_configure 'Ad Hoc Distributed Queries',1reconfigure--使用完毕后，记得一定要要关闭它，因为这是一个安全隐患,切记执行下面的SQL语句exec sp_configure 'Ad Hoc Distributed Queries',0reconfigureexec sp_configure 'show advanced options',0reconfigure --2005SELECT top 10 * FROM OPENDATASOURCE('SQLOLEDB','Data Source=.;User ID=sa;Password=geovindu;').geovinduDB.dbo.meetingApplyGO--openrowset使用OLEDB的一些例子select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;','select * from GEOVINDU.dbo.school') as tselect * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;',GEOVINDU.dbo.school) as tselect * from openrowset('SQLOLEDB','Server=(local);Trusted_Connection=yes;',GEOVINDU.dbo.school) as tselect * from openrowset('SQLOLEDB','(local)';'sa';'***','select * from GEOVINDU.dbo.school') as tselect * from openrowset('SQLOLEDB','(local)';'sa';'***',GEOVINDU.dbo.school) as tselect * 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 tselect * from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select * from GEOVINDU.dbo.school') as tselect * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;','select * from GEOVINDU.dbo.school') as tselect * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;',GEOVINDU.dbo.school) as tselect * 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 tselect * 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 initRESTORE DATABASE nwind_new FROM DISK = 'c:\backups\northwind\nwind.bak'WITHMOVE 'northwind' TO 'd:\Program Files\Microsoft SQLServer\Data\nwind_new.mdf'MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQLServer\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 * FROMOpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\temp\payroll.mdb"; User ID=Admin;Password=;')...employeesSELECT *FROM OPENROWSET ( BULK 'C:\data.txt',SINGLE_CLOB)AS a
