sql server 2000/2005 script
---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
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)