- 博客(0)
- 资源 (7)
- 收藏
- 关注
的常用的sql服务器检查/管理脚本
一些自己写得或者搜集来的常用的sql服务器检查/管理脚本。供大家参考。
GRANT VIEW SERVER STATE TO ['+ltrim(rtrim(@loginname))+']
GRANT VIEW ANY DEFINITION TO ['+ltrim(rtrim(@loginname))+']
GRANT VIEW ANY DATABASE TO ['+ltrim(rtrim(@loginname))+']
2013-03-21
sql语句教程
SQL语句教程
SQL语句教程(01) SELECT...........................................................................................................2
SQL语句教程(02) DISTINCT........................................................................................................2
SQL语句教程(03) WHERE............................................................................................................2
SQL语句教程(04) AND OR...........................................................................................................3
SQL语句教程(05) IN......................................................................................................................3
SQL语句教程(06) BETWEEN.......................................................................................................4
SQL语句教程(07) LIKE.................................................................................................................4
SQL语句教程(08) ORDER BY......................................................................................................5
SQL语句教程(09) 函数..................................................................................................................5
SQL语句教程(10) COUNT............................................................................................................6
SQL语句教程(11) Group By..........................................................................................................6
SQL语句教程(12) HAVING...........................................................................................................7
SQL语句教程(13) ALIAS...............................................................................................................7
SQL语句教程(14) 连接..................................................................................................................8
SQL语句教程(15) 外部连接..........................................................................................................9
SQL语句教程(16) Subquery..........................................................................................................9
SQL语句教程(17) UNION............................................................................................................10
SQL语句教程(18) UNION ALL...................................................................................................11
SQL语句教程(19) INTERSECT..................................................................................................11
SQL语句教程(20) MINUS............................................................................................................12
SQL语句教程(21) Concatenate....................................................................................................12
SQL语句教程(22) Substring........................................................................................................13
SQL语句教程(23) TRIM..............................................................................................................14
SQL语句教程(24) Create Table...................................................................................................14
SQL语句教程(25) Create View....................................................................................................15
SQL语句教程(26) Create Index...................................................................................................16
SQL语句教程(27) Alter Table......................................................................................................16
SQL语句教程(28) 主键................................................................................................................18
SQL语句教程(29) 外来键............................................................................................................18
SQL语句教程(30) Drop Table......................................................................................................19
SQL语句教程(31) Truncate Table...............................................................................................20
SQL语句教程(32) Insert Into.......................................................................................................20
SQL语句教程(33) Update............................................................................................................20
SQL语句教程(34) Delete..............................................................................................................21
2012-06-05
sql脚本优化
今天根据经验总结一些高级查询(SQL)及SQL效率优化,有时同样能达到查询效果,但也许系统所耗费的资源可能有很大差异,由于SQL书写的影响,同一功能同一性能不同写法SQL的影响。下面是sql语句优化的一些提示要点,主要讲述的是操作符优化过程。
IN 操作符
用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。
但是用IN的SQL性能总是比较低的,从sql执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:
sql试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
2012-06-05
sql server常用的服务器数据库状态查询脚本
dba常用的状态查询脚本等
EXEC sp_who2
exec sp_lock
EXEC sp_helpdb
dbcc sqlperf(logspace)
--查看当前数据库服务器名(xp_getnetname、HOST_NAME())
EXEC master..xp_getnetname
--查看服务器上所有Windows本地组的存储过程如下
EXEC master..xp_enumgroups
--查看磁盘信息的存储过程如下
EXEC [master].[dbo].[xp_availablemedia]
EXEC master..xp_fixeddrives
--请注意:其中,low free为以字节为单位的空闲空间,media type为驱动器类型,软驱为1,硬盘为2,CD-ROM为8。
--查看C盘文件信息的存储过程如下
EXEC master..xp_dirtree 'c:'
2012-06-05
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人