SQL Server Êý¾Ý¿â¹ÜÀí³£ÓõÄSQLºÍT SQLÓï¾ä
1. ²é¿´Êý¾Ý¿âµÄ°æ±¾
select @@version
2. ²é¿´Êý¾Ý¿âËùÔÚ»úÆ÷²Ù×÷ϵͳ²ÎÊý
exec master..xp_msver
3. ²é¿´Êý¾Ý¿âÆô¶¯µÄ²ÎÊý
sp_configure
4. ²é¿´Êý¾Ý¿âÆô¶¯Ê±¼ä
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
²é¿´Êý¾Ý¿â·þÎñÆ÷ÃûºÍʵÀýÃû
print 'Server Name: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance: ' + convert(varchar(30),@@SERVICENAME)
5. ²é¿´ËùÓÐÊý¾Ý¿âÃû³Æ¼°´óС
sp_helpdb
ÖØÃüÃûÊý¾Ý¿âÓõÄSQL
sp_renamedb 'old_dbname', 'new_dbname'
6. ²é¿´ËùÓÐÊý¾Ý¿âÓû§µÇ¼ÐÅÏ¢
sp_helplogins
²é¿´ËùÓÐÊý¾Ý¿âÓû§ËùÊôµÄ½ÇÉ«ÐÅÏ¢
sp_helpsrvrolemember
ÐÞ¸´Ç¨ÒÆ·þÎñÆ÷ʱ¹ÂÁ¢Óû§Ê±,¿ÉÒÔÓõÄfix_orphan_user½Å±¾»òÕßLoneUser¹ý³Ì
¸ü¸Äij¸öÊý¾Ý¶ÔÏóµÄÓû§ÊôÖ÷
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
×¢Òâ: ¸ü¸Ä¶ÔÏóÃûµÄÈÎÒ»²¿·Ö¶¼¿ÉÄÜÆÆ»µ½Å±¾ºÍ´æ´¢¹ý³Ì¡£
°Ñһ̨·þÎñÆ÷ÉϵÄÊý¾Ý¿âÓû§µÇ¼ÐÅÏ¢±¸·Ý³öÀ´¿ÉÒÔÓÃadd_login_to_aserver½Å±¾
7. ²é¿´Á´½Ó·þÎñÆ÷
sp_helplinkedsrvlogin
²é¿´Ô¶¶ËÊý¾Ý¿âÓû§µÇ¼ÐÅÏ¢
sp_helpremotelogin
8.²é¿´Ä³Êý¾Ý¿âÏÂij¸öÊý¾Ý¶ÔÏóµÄ´óС
sp_spaceused @objname
»¹¿ÉÒÔÓÃsp_toptables¹ý³Ì¿´×î´óµÄN(ĬÈÏΪ50)¸ö±í
²é¿´Ä³Êý¾Ý¿âÏÂij¸öÊý¾Ý¶ÔÏóµÄË÷ÒýÐÅÏ¢
sp_helpindex @objname
»¹¿ÉÒÔÓÃSP_NChelpindex¹ý³Ì²é¿´¸üÏêϸµÄË÷ÒýÇé¿ö
SP_NChelpindex @objname
clusteredË÷ÒýÊǰѼǼ°´ÎïÀí˳ÐòÅÅÁеģ¬Ë÷ÒýÕ¼µÄ¿Õ¼ä±È½ÏÉÙ¡£
¶Ô¼üÖµDML²Ù×÷Ê®·ÖƵ·±µÄ±íÎÒ½¨ÒéÓ÷ÇclusteredË÷ÒýºÍÔ¼Êø£¬fillfactor²ÎÊý¶¼ÓÃĬÈÏÖµ¡£
²é¿´Ä³Êý¾Ý¿âÏÂij¸öÊý¾Ý¶ÔÏóµÄµÄÔ¼ÊøÐÅÏ¢
sp_helpconstraint @objname
9.²é¿´Êý¾Ý¿âÀïËùÓеĴ洢¹ý³ÌºÍº¯Êý
use @database_name
sp_stored_procedures
²é¿´´æ´¢¹ý³ÌºÍº¯ÊýµÄÔ´´úÂë
sp_helptext '@
Ïà¹ØÎĵµ£º
±¾ÎÄÖ÷ÒªÄÚÈÝÊô×ªÔØ£¬µ«±ÊÕ߸ù¾Ý¸ÃÎÄÄÚÈݲâÊԳɹ¦£¬¹Ê·ÖÏíÓÚ´Ë¡£
±ÊÕßʵÑé»·¾³£ºWindows Server 2003 Enterprise Edition¡£
Ïȸø³öÔÎÄÁ´½Ó£¬ÉÔºóµ÷Õû¡£
ÔÎÄÁ´½ÓΪ£ºhttp://www.shilai.cn/2007/5/6/problems-of-installing-sql2005.aspx ......
ÔÚSQL ServerÖУ¬Èç¹û°Ñ±íµÄÖ÷¼üÉèΪidentityÀàÐÍ£¬Êý¾Ý¿â¾Í»á×Ô¶¯ÎªÖ÷¼ü¸³Öµ¡£ÀýÈ磺
create table customers (
id int identity(1,1) primary key not null,
name varchar(15)
);
insert into customers(name) values("name1"),("name2");
select id from customers;
²éѯ½á¹ûΪ£º
id
---
1
2
ÓÉ´Ë¿ ......
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--Èç¹û·Çºº×Ö×Ö·û£¬·µ»ØÔ×Ö·û
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
......
²»´íµÄ×ÊÁÏ,ת¹ýÀ´,·½±ãÈÕºó²é¿´Ê¹ÓÃ!!!
--¼à¿ØË÷ÒýÊÇ·ñʹÓÃ
alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name =
&index_name;
--ÇóÊý¾ÝÎļþµÄI/O·Ö²¼
select
df.name,phyrds,phywrts,phyblkrd,phyblkwrt,sin ......