sql server 2000/2005 script

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230

---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