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