SQL2000 tip

1.建立索引 create [UNIQUE or CLUSTERED or NONCLUSTERED] index index_name on table(column,...) 2.建立unique,clustered,nonclustered,primary key 约束 alter table tablename add constraint constraint_name [unique or clustered or nonclustered or primary key] (conlumn,...) 注：primary key 约束，默认为索引 3.建立Foreign key约束 alter table tablename (with nochek) add constraint constraint_name foreign key(conlumn,...) references anothertable(conlumn,...) anothertable(conlumn)为参照的主键 4.建立check约束/default约束 alter table tablename add constraint constraint_name Check(condition) alter table tablename add constraint constraint_name default 'default_value' for conlumn_name 5.删除约束 sp_helpconstraint table_name 查看一个表中的约束情况 alter table table_name drop constraint constraint_name 删除约束   6.删除索引 sp_helpindex table_name 查看表中索引情况 drop index table_name.index_name 删除索引 7.恢复备份 restore database putonrecord from disk= 'f:/putonrecord(完全).dat' with norecovery restore database putonrecord from disk= 'f:/putonrecord.dat' with recovery restore database DBname from disk= 'c:/DBname.bak' WITH recoverty,   MOVE 'OnSouth_Data' TO 'd:/DBname.mdf',   MOVE 'OnSouth_Log' TO 'e:/DBname.ldf' RESTORE LOG MyNwind FROM MyNwindLog1 WITH NORECOVERY RESTORE LOG MyNwind FROM MyNwindLog2 WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM' --追加备份的恢复方法 restore HEADERONLY from disk='c:/bak/bak.dat' --记录一下position的值 restore database from disk='c:/bak/bak.dat' with file=Position的值 8.察看备份文件信息 restore FILELISTONLY from disk='f:/stat/stat' restore HEADERONLY from disk='f:/stat/stat' restore LABELONLY from disk='f:/stat/stat' restore VERIFYONLY from disk='f:/stat/stat' --验证备份但不还原备份。检查备份集是否完整以及所有卷是否都可读。但是，RESTORE VERIFYONLY 不尝试验证备份卷中的数据结--构。如果备份有效，则返回"该备份集有效"。 9.导入/导出 BULK INSERT DB..TableName FROM 'd:/trvca.txt' with (fieldterminator='|',rowterminator='|/n') --Output to XLS exec master..xp_cmdshell 'bcp gtdjgl.dbo.gtdj out c:/a.xls -c -q -S"(local)" -U"sa" -P"sunny"' --Output to XLS with queryout exec master..xp_cmdshell 'bcp "select qymc from sydjgl.dbo.qydj" queryout c:/a.xls -c -q -S"(local)" -U"sa" -P"sunny"' --Input from XLS exec master..xp_cmdshell 'bcp gtdjgl.dbo.gtdj in c:/a.xls -c -q -S"(local)" -U"sa" -P"sunny"' SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:/xyz.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...sheetname$ --读取txt文件 SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Text;Database=E:/;' , 'SELECT * FROM aa#txt') --将sql文件导入 osql -U 用户名 -P 密码 -i c:/**1.sql 10.查看索引情况 select    table_Name=sysobjects.Name ,     index_Name=sysindexes.Name,     Type=sysobjects.type,     分配索引页=sysindexes.reserved,     使用索引页=sysindexes.used,     叶子层页=sysindexes.Dpages,     非叶子层页=sysindexes.used-sysindexes.Dpages,     rows=sysindexes.rowcnt from sysindexes left outer join sysobjects on sysindexes.id=sysobjects.id where sysindexes.indid>0 and sysindexes.indid<255 and sysindexes.status & 64=0 --若发现非叶子层的页数为负数,最好是运行DBCC UPDATEUSAGE ('dbname','tbname','ixname')来更新一下sysindexes的信息 注意indid列的取值 1 = 聚集索引 >1 = 非聚集 255 = 具有 text 或 image 数据的表条目 11.查看表锁情况 if object_id('tempdb..#lock')>0 drop table #lock go create table #lock(     spid int,     dbid int,     ObjId int,     IndID int,     Type varchar(10),     Resource varchar(100),     Mode varchar(10),     Status varchar(10) ) insert into #lock exec sp_lock select dbName=d.Name,ObjName=o.Name,l.* from master..sysdatabases D,#lock L,sysobjects O where D.dbid=L.dbid and O.ID=L.ObjID 12.消除孤立用户 exec sp_addlogin 'LoginUser' use db go exec sp_grantdbaccess 'LoginUser','db_User' --在当前数据库中添加帐户(public) use db go exec sp_revokedbaccess 'LoginUser' --从数据库中删除帐户 use db go exec sp_grantdbaccess 'LoginUser' --重新为数据库添加账号 或者 use db go sp_change_users_login 'Report' --显示存在孤立用户的数据库帐号 sp_change_users_login 'Auto_Fix','sameName' --将数据库帐号='sameName'与登陆帐号='sameName'的建立连接 sp_change_users_login 'Update_One', 'dbUser', 'LoginUser' --将db数据库帐号dbUser与登陆帐号LoginUser建立连接 或者 用dts来导入帐号信息： [在源数据库上新建一个包  然后将“传输登陆任务”从左边工具栏的 拖到右边框里 在弹出的框中设置相应的登录信息，最后运行包即可] 或者 bcp master..syslogins out localpath/syslogins.dat /N /S current_primary_servername /U sa /P sa_password EXEC xp_cmdshell 'copy localpath/syslogins.dat destination_share' EXEC sp_resolve_logins @dest_db = 'dbname', @dest_path = 'destination_path', @filename = 'filename' GO 注意：使用dts或sp_resolve_logins最后还都是要作一遍sp_change_user_login才能将登陆帐号与db帐号连接起来 可以用sp_helpuser来察看数据库账号情况 可以用sp_helplogins来查看登陆账号情况 13.添加帐号 exec sp_addlogin 'sys_accounts','pass','default db' use db go exec sp_grantdbaccess 'sys_accounts','db_accounts' --添加到当前数据库下 sp_addrolemember 'db_owner','db_accounts' --调整数据库登陆角色为db_owner 或者 (1).exec sp_addlogin 'sys_accounts','pass','default db' --sp_droplogin 为删除登陆帐号 (2).sp_addsrvrolemember 'sys_accounts',sysadmin --调整系统登陆角色为administrator --更改默认数据库 sp_defaultdb 'sa','newdb' 14.更改数据所有者 use db go sp_changedbowner 'newLogin' --将db库dbo默认的系统登陆帐号(sa)更改为新系统登陆帐号newLogin,也就是说newlogin账号有了db数据库的db_owner权限                             --说得再白一些，newLogin登陆账号，将拥有db数据库的db_owner权限，但他却不需要在db中添加新的数据库账号（也就是                 --说在db数据库中没有newLogin这个帐号，而是将dbo帐号与newLogin系统帐号连接起来了）                 --因为，它直接利用dbo这个数据库账号。 use db go exec sp_changeobjectowner 'gsscowner.sp_tjbm_sci','dbo' --更改表、存储、试图对象的所有者，'dbo'处可以使用其他的数据库账号，但非系统账号 15.分布连接 SELECT * FROM Opendatasource('SQLOLEDB','Data Source=ServerName;User ID=MyUID;Password=MyPass').Northwind.dbo.Categories 16.查询表使用情况 EXEC sp_MSforeachtable @command1='sp_spaceused ''?''',@command2='sp_mstablespace ''?''' 17.测试T-SQL的执行效率 dbcc DropCleanBuffers --清除缓冲区 dbcc FreeProcCache --清除过程高速缓存 set statistics io on select count(*) from one --报告查询中包含的每个表所进行的I/O活动信息，包含扫描的行数和读取的次数。 set statistics io off set statistics time on select count(*) from one --报告关于CPU和查询的全部执行时间及其查询结果而运行的其他系统过程(如：编译和存储查询的过程)的信息 set statistics time off set showplan_all on go select count(*) from one --以文本的形式报告查询执行的详细计划 go set showplan_all off 18.整理索引 dbcc showcontig(tbName,ixName) --with ALL_INDEXES - 扫描页数.....................................: 3 --总的分页数目 - 扫描扩展盘区数...............................: 2 --一个扩展分区相当于8个连续的分页  - 扩展盘区开关数...............................: 1 --当dbcc顺着分页指针浏览整个结构时，扩展分区间切换了的次数。说的白一点就是在                                                                                  --扫描中一个页与前一页位于不同盘区的次数 - 每个扩展盘区上的平均页数.....................: 1.5--用 扩展分区数(2)/扫描页数(3) 得出的数据 - 扫描密度［最佳值:实际值］....................: 50.00%［1:2］--最佳值：3个分页应该是在一个扩展分区中；实际值：扩展盘区更改的                                   --实际次数。2的由来实际是由盘区开关数+1得出的。              - 逻辑扫描碎片.................................: 0.00%--Out of order pages/扫描页数(3) .                             Out of order pages  含义：本来按照分页的连续性，数据分布的分页应该是                                 紧紧挨着的，但由于数据的修改、删除而整页的搬移，造成了数据分布的不连                             续性，不连续的页就是out of orders pages .                                 --在这里没有任何一个分页不连续，因此为0 - 扩展盘区扫描碎片.............................: 0.00%--与上面的意思基本一致，仅仅是将分页换成了扩展分区 - 每页上的平均可用字节数.......................: 246.7--代表每页空下来未放数据的字节数（平均值） - 平均页密度（完整）...........................: 96.95%--代表分页填满的程度（平均值） backup log logname to disk='D:/log/db.log' alter database dbName set recovery simple /*set recovery bulk_logged*/set recovery full dbcc DBReIndex ('db.owner.tbName','ixName','Fill Factor') --DBCC INDEXDEFRAG (dbName, tbName, ixName) alter database dbName set recovery full backup database dbname to disk='D:/db/db.bak' 19.sp_server_info 用来察看数据库系统状态 20.alter database northwind set MULTI_USER /single_user --设置数据库为单用户/多用户访问模式   sp_dboption 'pubs', 'single user' --判断数据库是单用户还是多用户   sp_helpdb --也可判断   SELECT DATABASEPROPERTY('master',IsSingleUser) 21.alter database northwind ... WITH ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE --...为数据库设置操作 --回滚未完成的事务 22.exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','10.50.80.11'    --添加连接服务器   exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','suntting'   sp_dropserver 'srv_lnk', 'droplogins'--删除登陆信息   sp_helpserver 23.DBCC UPDATEUSAGE/sp_refreshview/sp_recompile/sp_spaceused objectName,true/false   DBCC UPDATEUSAGE --报告和更正 sysindexes 表的不正确内容，可能会导致 sp_spaceused 产生不正确的空间使用报表。   sp_refreshview --解决重建视图的问题，还可以通过重建，发现系统中视图的语法错误。   sp_recompile    --使存储过程和触发器在下次运行时重新编译,若调整了表结构，发现存储过程运行很慢，可以用这个来解决   sp_spaceused objectName,true/false --但每次更新表或索引时,sysindexes表中的数据并不立刻更新,因此sp_spaceused输出的结果不能反映表或索引的空间总数,需要加参数                                      --true强制更新   24.修改默认的数据文件组 ALTER DATABASE dbName MODIFY FILEGROUP dbGroupName/[PRIMARY] DEFAULT 25.如何把20060126转成2006-01-26 select convert(varchar(10),cast('20060126' as datetime),120) 26.随机选出数据 select top 1  * from table order by newid() 27.怎样删除sa (1).sa可以删除但不建议你删除，其实只要设置好密码不用它既可 删除办法： 先获取修改系统表的权限 sp_configure ’allow updates’, 1 go RECONFIGURE WITH OVERRIDE go update sysxlogins set name='modify' where sid=0x01 update sysxlogins set sid=0x1823BA32F3E6F84EBD5EA43973D810AC where name='modify' sp_droplogin 'modify'删除刚刚修改的账号即可 28.删除/添加扩展存储过程 (1).exec master..sp_dropextendedproc 'xp_cmdshell' --删除 (2).exec master..sp_addextendedproc xp_cmdshell, 'xplog70.dll' (3).exec master..sp_helpextendedproc [exprocname] 29.select CAST('Aug 1 1996 12:00AM' AS datetime)出错 设置默认语言即可 SET LANGUAGE us_english select CAST('Aug 1 1996 12:00AM' AS datetime) 30.强制使用索引 with(index(索引名)) 31.跳过被锁定的行 select * from tb with(READPAST) 强制跳过聚集索引的索引页和索引叶节点页（数据页）中行锁定的部分。 select * from test with(FASTFIRSTROW) where c1<>1 32.dts包转移 先打开dts包，选择“另存为”，在位置处选择“结构化存储文件”，将包导出 导入时，右键点“数据转换服务”，选择“打开包”，将刚刚导出的包导入即可 33.更新系统表 Use Master Go sp_configure 'allow updates', 1 reconfigure with override Go 34.获取字节长度 select DATALENGTH('str') 35.使用自定义的索引 select count(*) from equipment with (index(PK_equipment)) 36.目前登陆名、数据帐号 select SYSTEM_USER select user select suser_sname 37.查询语言支持 SELECT * FROM ::fn_helpcollations() --系统所支持的所有的语言 select DATABASEPROPERTYEX( 'dbname','Collation') --查询当前某库使用的语言 sp_helpsort --查看服务器默认的排序情况 要看表中的排序情况，请参看syscolumns 或者 sp_help 'tbname' 39.DBCC check修复步骤 USE master go --EXEC sp_dboption 'equipment', 'single user', 'TRUE' alter database equipment set single_user with ROLLBACK IMMEDIATE go --dbcc checkdb(equipment,REPAIR_REBUILD) dbcc checkdb(equipment,REPAIR_ALLOW_DATA_LOSS) go alter database equipment set MULTI_USER  with ROLLBACK IMMEDIATE go 40.查询磁盘使用空间 EXECUTE master..xp_fixeddrives 41.快速查询表的行数 SELECT @num_rows=rowcnt,@min_indid=indid FROM sysindexes WHERE id=OBJECT_ID(@qualified_table_name)and indid < 2 42.检验两个表是否相同 select sum (convert(numeric, binary_checksum(*/colname) ) ) from tbname 43.获取一个错误的返回值方法 (1).declare @ret int EXEC @ret =  sp_addlinkedserver 'srv_lnk2','','SQLOLEDB','10.50.80.14' select @ret (2).用@@error来判断 44.怎么获得所有的odbc数据源名称？ xp_enumdsn 44.获取类似sysobjects的属性 exec sp_tables @table_type="'TABLE'" exec sp_tables select * from INFORMATION_SCHEMA.TABLES where table_type='Base TABLE' and table_name<>'dtproperties' 45.sysprocesses的应用 select spid,uid,syslogins.name,login_time,net_address from sysprocesses,syslogins where sysprocesses.sid=syslogins.sid 46.使用索引服务 sp_addlinkedserver filesystem,'indexing service','MSIDXS','doc' select * from openquery(filesystem,'select directory,filename,docauthor,size,create from scope() where contains(contents,''模块'')') 47.ISNUMERIC  确定表达式是否为一个有效的数字类型。 48.len(trim()) 判断是否为空 49.让自动编号的ID从1开始 truncate table tablename --清表后会自动回到原始 dbcc checkident('tablename',reseed,1) --重新设置为1 SET IDENTITY_INSERT Tablename ON --设置这个选项后就可以insert自定义的id值了 alter table t_b add newcol int identity(1,1) not null 50.汉字排序问题 --按笔划排序 select * from sheet2 order by col1 collate Chinese_PRC_Stroke_CS_AS_KS_WS Select * From sheet2 Order By col1 Collate Chinese_PRC_Stroke_ci_as  --按拼音排序 select * from sheet2 order by col1 collate Chinese_PRC_CS_AS_KS_WS 51.使用BCP将全局临时表的数据导出到文本： select * into ##T from master..sysobjects DECLARE @cmd sysname SET @cmd = 'bcp "##T" out "D:/T.txt" -c -S(local) -Usa -P123456' EXEC master..xp_cmdshell @cmd drop table ##T 注意：BCP不能使用局部临时表，但可以使用全局临时表。 52.FOREIGN KEY还可以定义为引用另一表的UNIQUE约束 FOREIGN KEY 约束并不仅仅只可以与另一表的 PRIMARY KEY 约束相链接，它还可以定义为引用另一表的 UNIQUE 约束。 大部分情况下，我们都是引用另一个表的PRIMARY KEY，很少去引用另一个表的UNIQUE约束，下面是引用另一个表的UNIQUE约束的代码： --创建表 create table tPK ( id int, col1 int ) --添加唯一约束 create unique index IX_ID on tPK(id) --创建另一个表 create table tFK ( id int, col2 int ) --添加引用唯一约束的外键 alter table tFK add constraint FK_1 foreign key (id) references tPK(id) 利用这个功能，可以实现对于候选键的外键约束。 53.查看索引或统计最后一次更新的时间： select object_name(id) as table_name,name as index_name,STATS_DATE(id,indid) as last_update_time from sysindexes where STATS_DATE(id,indid ) is not null and objectproperty(id,'ismsshipped')=0 54.怎样知道一个数据库备份文件是用sqlserver自身备份的，还是用第三方软件备份的呢？ 其实很简单，只需针对备份文件执行RESTORE HEADERONLY命令即可。例如： RESTORE HEADERONLY FROM disk=N'D:/db1.bak' 输出结果集里的其中4个字段，分别如下，大家一看就明白了吧： SoftwareVendorId    int    软件供应商标识号。              对于 SQL Server，该号码是 4608（用十六进制表示是 0x1200）。 SoftwareVersionMajor    int    创建备份集的服务器主要版本号。    创建备份集的软件的主要版本号 SoftwareVersionMinor    int    创建备份集的服务器次要版本号。    创建备份集的软件的次要版本号 SoftwareVersionBuild    int    创建备份集的服务器内部版本号码。NULL