一些常用的SQL语句

        1.在查询结果中显示列名：

　　a.用as关键字：

select  name  as   ' 姓名 '   from  students  order   by  age

 　　b.直接表示：

select  name  ' 姓名 '   from  students  order   by  age

　　2.精确查找:

　　a.用in限定范围：

select  name  ' 姓名 '   from  students  order   by  age

　　b.between...and：

select   *   from  students  where  age  between   20   and   30

 　　c.“=”：

select   *   from  students  where  name  =   ' 李山 '  

 　　d.like:

select   *   from  students  where  name  like   ' 李% '

 (注意查询条件中有“%”，则说明是部分匹配，而且还有先后信息在里面，即查找以“李”开头的匹配项。所以若查询有“李”的所有对象，应该命令：'%李%';若是第二个字为李，则应为'_李%'或'_李'或'_李_'。)

　　e.[]匹配检查符：

select   *   from  courses  where  cno  like   ' [AC]% '

 (表示或的关系，与"in(...)"类似，而且"[]"可以表示范围，如：

select   *   from  courses  where  cno  like   ' [A-C]% '

)

　　3.对于时间类型变量的处理

　　a.smalldatetime：直接按照字符串处理的方式进行处理，例如：

select   *   from  students  where  birth  >   =   ' 1980-1-1 '   and  birth  <=   ' 1980-12-31 '  

 　　4.集函数

　　a.count()求和，如：

select   count ( * )  from  students (求学生总人数)

　　b.avg(列)求平均，如：

select   avg (mark)  from  grades  where  cno = ’B2’

 　　c.max(列)和min(列)，求最大与最小

　　5.分组group

　　常用于统计时，如分组查总数：　　

select  gender, count (sno)   from  students  group   by  gender

　　(查看男女学生各有多少)

　　注意：从哪种角度分组就从哪列"group by"

　　对于多重分组，只需将分组规则罗列。比如查询各届各专业的男女同学人数 ，那么分组规则有：届别(grade)、专业(mno)和性别(gender)，所以有"group by grade, mno, gender"　　

select  gender, count (sno)   from  students  group   by  gender

　　通常group还和having联用，比如查询1门课以上不及格的学生，则按学号(sno)分类有：　　

select  sno, count ( * )  from  grades  where  mark < 60 group   by  sno　 having   count ( * ) > 1

　　6.UNION联合

　　合并查询结果，如：　　

SELECT   *   FROM  students

　　 WHERE  name  like  ‘张 % ’

　　 UNION   [ ALL ]

　　 SELECT   *   FROM  students

　　 WHERE  name  like  ‘李 % ’

 　　7.多表查询

　　a.内连接　　

select  g.sno,s.name,c.coursename 

　　 from  grades g  JOIN  students s  ON  g.sno = s.sno

　　 JOIN  courses c  ON  g.cno = c.cno

 　　(注意可以引用别名)

　　b.外连接

　　b1.左连接　

select  g.sno,s.name,c.coursename 

　　 from  grades g  JOIN  students s  ON  g.sno = s.sno

　　 JOIN  courses c  ON  g.cno = c.cno

 　　左连接特点：显示全部左边表中的所有项目，即使其中有些项中的数据未填写完全。

　　左外连接返回那些存在于左表而右表中却没有的行，再加上内连接的行。

　　b2.右连接

　　与左连接类似

　　b3.全连接　　

select  sno,name,major 

　　 from  students  FULL   JOIN  majors  ON  students.mno = majors.mno

 　　两边表中的内容全部显示

　　c.自身连接　　

select  c1.cno,c1.coursename,c1.pno,c2.coursename 

　　 from  courses c1,courses c2  where  c1.pno = c2.cno

　　采用别名解决问题。

　　d.交叉连接　　

select  lastname + firstname  from  lastname  CROSS   JOIN  firstanme

 　相当于做笛卡儿积

    8.嵌套查询

　　a.用关键字IN,如查询李山的同乡：　

　 select   *   from  students

　　 where  native  in  ( select  native  from  students  where  name ='  李山')

　　b.使用关键字EXIST,比如，下面两句是等价的：　　

select   *   from  students

　　 where  sno  in  ( select  sno  from  grades  where  cno =' B2')

　　 select   *   from  students  where   exists  

　　( select   *   from  grades  where  

　　grades.sno = students.sno  AND  cno =' B2')

　　9.关于排序order

　　a.对于排序order，有两种方法：asc升序和desc降序

　　b.对于排序order,可以按照查询条件中的某项排列，而且这项可用数字表示，如：　　

select  sno, count ( * ) , avg (mark)  from  grades 

　　 group   by  sno

　　 having   avg (mark) > 85

　　 order   by   3  

　　10.其他

　　a.对于有空格的识别名称，应该用"[]"括住。

　　b.对于某列中没有数据的特定查询可以用null判断，如

select  sno,courseno  from  grades  where  mark  IS   NULL

　　c.注意区分在嵌套查询中使用的any与all的区别，any相当于逻辑运算“||”而all则相当于逻辑运算“&&”

　　d.注意在做否定意义的查询是小心进入陷阱：

　　如，没有选修‘B2’课程的学生 ：　　

select  students. *

　　 from  students, grades

　　 where  students.sno = grades.sno

　　 AND  grades.cno  <>  ’B2’ 

　　上面的查询方式是错误的，正确方式见下方：　　

select   *   from  students

　　 where   not   exists  ( select   *   from  grades 

　　 where  grades.sno = students.sno  AND  cno = ' B2 ' ) 

　　11.关于有难度多重嵌套查询的解决思想：

　　如，选修了全部课程的学生：　　

select   *

　　 from  students

　　 where   not   exists  (  select   *

　　 from  courses　

　　 where   NOT   EXISTS  

　　( select   *

　　 from  grades

　　 where  sno = students.sno

　　 AND  cno = courses.cno))

     说明：复制表(只复制结构,源表名：a 新表名：b)

　　SQL:

select   *   into  b  from  a  where   1 <> 1  

　　

　　说明：拷贝表(拷贝数据,源表名：a 目标表名：b)

　　SQL:

insert   into  b(a, b, c)  select  d,e,f  from  b

　　说明：显示文章、提交人和最后回复时间

　　SQL:

select  a.title,a.username,b.adddate  from   table  a,( select   max (adddate) adddate  from   table   where   table .title = a.title) b 

　 说明：外连接查询(表名1：a 表名2：b)

　 SQL:

select  a.a, a.b, a.c, b.c, b.d, b.f  from  a  LEFT  OUT  JOIN  b  ON  a.a  =  b.c 

　　
　　说明：日程安排提前五分钟提醒

　　SQL: 

select   *   from  日程安排  where   datediff ( ' minute ' ,f开始时间, getdate ()) > 5

 　　
　　说明：两张关联表，删除主表中已经在副表中没有的信息

　　SQL: 　

delete   from  info  where   not   exists  (  select   *   from  infobz  where  info.infid = infobz.infid ) 

　　

　　说明：--

　　SQL: 　

SELECT

   A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE 

  FROM  TABLE1,( SELECT  X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE  FROM  ( SELECT  NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND  FROM  TABLE2  WHERE  TO_CHAR(UPD_DATE, ' YYYY/MM ' )  =  TO_CHAR(SYSDATE,  ' YYYY/MM ' )) X, ( SELECT  NUM, UPD_DATE, STOCK_ONHAND  FROM  TABLE2  WHERE  TO_CHAR(UPD_DATE, ' YYYY/MM ' )  =  TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,  ' YYYY/MM ' )  & brvbar; & brvbar;  ' /01 ' , ' YYYY/MM/DD ' )  -   1 ,  ' YYYY/MM ' ) ) Y,  WHERE  X.NUM  =  Y.NUM （ + ） AND  X.INBOUND_QTY  +  NVL(Y.STOCK_ONHAND, 0 )  <>  X.STOCK_ONHAND ) B  WHERE  A.NUM  =  B.NUM 

　　

　　说明：--

　　SQL: 　

select   *   from  studentinfo  where   not   exists ( select   *   from  student  where  studentinfo.id = student.id)  and  系名称 = ' "&strdepartmentname&" '   and  专业名称 = ' "&strprofessionname&" '   order   by  性别,生源地,高考总成绩 

       说明： 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）

　　SQL: 　

SELECT  a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate,  ' yyyy ' )  AS  telyear,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 01 ' , a.factration))  AS  JAN,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 02 ' , a.factration))  AS  FRI,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 03 ' , a.factration))  AS  MAR,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 04 ' , a.factration))  AS  APR,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 05 ' , a.factration))  AS  MAY,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 06 ' , a.factration))  AS  JUE, SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 07 ' , a.factration))  AS  JUL,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 08 ' , a.factration))  AS  AGU,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 09 ' , a.factration))  AS  SEP,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 10 ' , a.factration))  AS  OCT,  SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 11 ' , a.factration))  AS  NOV, SUM (decode(TO_CHAR(a.telfeedate,  ' mm ' ),  ' 12 ' , a.factration))  AS   DEC   FROM  ( SELECT  a.userper, a.tel, a.standfee, b.telfeedate, b.factration  FROM  TELFEESTAND a, TELFEE b  WHERE  a.tel  =  b.telfax) a  GROUP   BY  a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate,  ' yyyy ' ) 

　　

　　说明：四表联查问题：

　　SQL:

select   *   from  a  left   inner   join  b  on  a.a = b.b  right   inner   join  c  on  a.a = c.c  inner   join  d  on  a.a = d.d  where  ..... 　　 

　　说明：得到表中最小的未使用的ID号

　　SQL:

　 SELECT  ( CASE   WHEN   EXISTS ( SELECT   *   FROM  Handle b  WHERE  b.HandleID  =   1 )  THEN   MIN (HandleID)  +   1   ELSE   1   END )  as  HandleID  FROM  Handle  WHERE   NOT  HandleID  IN  ( SELECT  a.HandleID  -   1   FROM  Handle a)　

　

      说明：数据库字段列表

      SQL：

SELECT  
 ( case   when  a.colorder = 1   then  d.name  else   ''   end ) N ' 表名 ' ,
 a.colorder N ' 字段序号 ' ,
 a.name N ' 字段名 ' ,
 ( case   when   COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1   then   ' √ ' else   ''   end ) N ' 标识 ' ,
 ( case   when  ( SELECT   count ( * )
  FROM  sysobjects
  WHERE  (name  in
           ( SELECT  name
           FROM  sysindexes
           WHERE  (id  =  a.id)  AND  (indid  in
                    ( SELECT  indid
                    FROM  sysindexkeys
                    WHERE  (id  =  a.id)  AND  (colid  in
                             ( SELECT  colid
                             FROM  syscolumns
                             WHERE  (id  =  a.id)  AND  (name  =  a.name)))))))  AND
        (xtype  =   ' PK ' )) > 0   then   ' √ '   else   ''   end ) N ' 主键 ' ,
 b.name N ' 类型 ' ,
 a.length N ' 占用字节数 ' ,
  COLUMNPROPERTY (a.id,a.name, ' PRECISION ' )  as  N ' 长度 ' ,
  isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 )  as  N ' 小数位数 ' ,
 ( case   when  a.isnullable = 1   then   ' √ ' else   ''   end ) N ' 允许空 ' ,
  isnull (e. text , '' ) N ' 默认值 ' ,
  isnull (g. [ value ] , '' )  AS  N ' 字段说明 '

FROM   syscolumns  a  left   join  systypes b 
on   a.xtype = b.xusertype
inner   join  sysobjects d 
on  a.id = d.id   and   d.xtype = ' U '   and   d.name <> ' dtproperties '
left   join  syscomments e
on  a.cdefault = e.id
left   join  sysproperties g
on  a.id = g.id  AND  a.colid  =  g.smallid  
order   by   object_name (a.id),a.colorder

            说明：检查数据库物理文件大小增长

            SQL：

-- Script that will generate an alert if we've had filegrowth
USE  master
GO

-- Error message. Procedure raises error 50001. Adjust to your needs
EXEC  sp_addmessage   @msgnum   =   50001  ,  @severity   =   12 ,  @with_log   =   ' true '
,  @msgtext   =   ' Filegrowth has occured to file %s in database %s. '
,  @replace   =   ' REPLACE '
GO
-- Create alert for errormessage 50001. Adjust to your needs
USE  msdb
EXEC  sp_add_alert  @name   =   ' FileGrowth ' ,  @message_id   =   50001 , 
     @notification_message   =   ' We '' ve had file growt. '
-- Specify operators to alert. Adjust operatorname to your environment.
EXEC  sp_add_notification  ' FileGrowth ' ,  ' SQLAdmins ' ,  1
USE  master
GO

-- Clean up so we can run several times
DROP   TABLE  master.dbo.dbmsys_dbfiles
GO
DROP   PROC  sp_dbm_checkfilegrowth
GO

-- This is the proc that we'll schedule execution of
CREATE   PROC  sp_dbm_checkfilegrowth  AS
/**/ /*******************************************************************************/
/**/ /* This procedure writes an error message to eventlog if there has occured     */
/**/ /* growth for any file in any database. The procedure is designed to run as    */
/**/ /* a scheduled job regurarly.  Adjust error number, error message and operator */
/**/ /* in beginning of script file and error number in proc code.                  */
/**/ /* The procedure takes no parameters:                                          */
/**/ /* Tested on version 7.0 and 8.0.                                              */
/**/ /*******************************************************************************/
SET  NOCOUNT  ON
-- Get version number and verify supported version
DECLARE   @ver   varchar ( 7 )
SELECT   @ver   =   CASE
  WHEN   CHARINDEX ( ' 7.00 ' ,  @@VERSION )  >   0   THEN   ' 7.00 '
  WHEN   CHARINDEX ( ' 8.00 ' ,  @@VERSION )  >   0   THEN   ' 8.00 '
  ELSE   ' Unknown '
END  
IF   @ver   =   ' Unknown '
BEGIN
    RAISERROR ( ' Unsupported version of SQL Server. ' , 16 , 1 )
    RETURN   - 101
END  

-- Declare variables section
DECLARE   @dbname  sysname,  @filename   varchar ( 260 ),  @cursize   int ,  @lastsize   int

-- Verify that table to hold information exists. 
-- If not, create and issue message that this is first execution and table was created
IF   NOT   EXISTS ( SELECT   *   FROM  master.dbo.sysobjects  WHERE  name  =   ' dbmsys_dbfiles ' )
  BEGIN
  CREATE   TABLE  master.dbo.dbmsys_dbfiles(dbname sysname  NOT   NULL ,dbfilename sysname  NOT   NULL ,mb_size  INT   NOT   NULL
     CONSTRAINT  pk_dbmsys_dbfiles  PRIMARY   KEY (dbname, dbfilename))
  RAISERROR ( ' First execution of sp_dbm_checkfilegrowth. Table master.dbo.dbmsys_dbfile created. ' ,  12 ,  1 )
  RETURN
  END

-- For each database name in sysdatabases
DECLARE  db  CURSOR   FOR   SELECT  CATALOG_NAME  FROM  INFORMATION_SCHEMA.SCHEMATA
OPEN  db
FETCH   NEXT   FROM  db  INTO   @dbname
WHILE   @@FETCH_STATUS   =   0
BEGIN
  -- For each file in this database
  EXEC ( ' DECLARE dbfile CURSOR FOR SELECT RTRIM(filename), size FROM  '   +   @dbname   +   ' ..sysfiles ' )
  OPEN  dbfile
  FETCH   NEXT   FROM  dbfile  INTO   @filename ,  @cursize
  WHILE   @@FETCH_STATUS   =   0
  BEGIN
   -- If we don't have the an entry for the file in our table, insert it
   IF   NOT   EXISTS ( SELECT   *   FROM  master.dbo.dbmsys_dbfiles  WHERE  dbfilename  =   @filename )
     INSERT  master.dbo.dbmsys_dbfiles (dbname, dbfilename, mb_size)  VALUES ( @dbname ,  @filename ,  @cursize )
   ELSE
   BEGIN
     -- Get file size last time we executed
     SELECT   @lastsize   =  mb_size  FROM  master.dbo.dbmsys_dbfiles  WHERE  dbfilename  =   @filename
     -- Did we have a filegrowth?
     IF   @cursize   >   @lastsize
       RAISERROR ( 50001 ,  - 1 ,  1 ,  @filename ,  @dbname )
     -- Refresh sys_dbfiles to reflect current file size
     UPDATE  master.dbo.dbmsys_dbfiles  SET  mb_size  =   @cursize   WHERE  dbfilename  =   @filename   AND  dbname  =   @dbname
   END
   FETCH   NEXT   FROM  dbfile  INTO   @filename ,  @cursize
  END
  CLOSE  dbfile
  DEALLOCATE  dbfile
  FETCH   NEXT   FROM  db  INTO   @dbname
END
-- DELETE entries in sys_dbfiles for db's that no longer exists
DELETE  master.dbo.dbmsys_dbfiles  WHERE  dbname  NOT   IN ( SELECT  CATALOG_NAME  FROM  INFORMATION_SCHEMA.SCHEMATA)
CLOSE  db
DEALLOCATE  db
GO


-- Use EM to schedule execution of master..sys_checkfilegrowth with desired frequency

 

              说明：Kill All Connecting Process（杀死所有进程）

              SQL：

USE  master
GO
CREATE   PROC  sp_dbm_kill_users  @dbname  sysname,  @delay   DATETIME   =   ' 00:00 '   AS
/**/ /*******************************************************************************/
/**/ /* This procedure executes KILL for all connections in the specified database. */
/**/ /* The procedure can be mofified so it kills all connections for a server.     */
/**/ /* The procedure takes the following parameters:                               */
/**/ /*   @dbname SYSNAME (required): Database name.                                */
/**/ /*   @delay DATETIME (optional) [0] : Optional delay (s) before resume.        */
/**/ /* Tested on verion 6.5, 7.0 and 8.0.                                          */
/**/ /*******************************************************************************/
SET  NOCOUNT  ON
-- Get version number and verify supported version
DECLARE   @ver   VARCHAR ( 7 )
SELECT   @ver   =   CASE
  WHEN   CHARINDEX ( ' 6.50 ' ,  @@VERSION )  >   0   THEN   ' 6.50 '
  WHEN   CHARINDEX ( ' 7.00 ' ,  @@VERSION )  >   0   THEN   ' 7.00 '
  WHEN   CHARINDEX ( ' 8.00 ' ,  @@VERSION )  >   0   THEN   ' 8.00 '
  ELSE   ' Unknown '
END  
IF   @ver   =   ' Unknown '
BEGIN
    RAISERROR ( ' Unsupported version of SQL Server. ' , 16 , 1 )
    RETURN   - 101
END  

DECLARE  loop_name INSENSITIVE  CURSOR   FOR
   SELECT  spid
    FROM  master..sysprocesses
    WHERE  dbid  =   DB_ID ( @dbname )

OPEN  loop_name
DECLARE   @conn_id   SMALLINT
DECLARE   @exec_str   VARCHAR ( 255 )
FETCH   NEXT   FROM  loop_name  INTO   @conn_id
WHILE  ( @@fetch_status   =   0 )
   BEGIN
     SELECT   @exec_str   =   ' KILL  '   +   CONVERT ( VARCHAR ( 7 ),  @conn_id )
     EXEC (  @exec_str  )
     FETCH   NEXT   FROM  loop_name  INTO   @conn_id
   END
DEALLOCATE  loop_name

WAITFOR  DELAY  @delay
GO

/**/ /* Sample Execution:
EXEC sp_dbm_kill_users pubs
EXEC sp_dbm_kill_users @dbname = pubs, @delay = '00:00:05'
*/

            说明：发送用户信息

            SQL：

USE  master
GO
CREATE   PROC  sp_dbm_notify_users  @msg   VARCHAR ( 255 )  AS
/**/ /*******************************************************************************/
/**/ /* This procedure does a NET SEND to all connected computers.                  */
/**/ /* Requires that the messenger service is running on the client.               */
/**/ /* The bat file is a sample showing how a message can be sent from the OS.  A  */
/**/ /* shortcut to the bat file can be placed on the desktop, for instance         */
/**/ /* The procedure takes the following parameter:                                */
/**/ /*   @msg VARCHAR(255) (required): The message to be sent                      */
/**/ /* Tested on version 6.5, 7.0 and 8.0.                                         */
/**/ /*******************************************************************************/
SET  NOCOUNT  ON
-- Get version number and verify supported version
DECLARE   @ver   VARCHAR ( 7 )
SELECT   @ver   =   CASE
  WHEN   CHARINDEX ( ' 6.50 ' ,  @@VERSION )  >   0   THEN   ' 6.50 '
  WHEN   CHARINDEX ( ' 7.00 ' ,  @@VERSION )  >   0   THEN   ' 7.00 '
  WHEN   CHARINDEX ( ' 8.00 ' ,  @@VERSION )  >   0   THEN   ' 8.00 '
  ELSE   ' Unknown '
END  
IF   @ver   =   ' Unknown '
BEGIN
    RAISERROR ( ' Unsupported version of SQL Server. ' , 16 , 1 )
    RETURN   - 101
END  

-- Declare variables section
DECLARE  loop_name INSENSITIVE  CURSOR   FOR
   SELECT   DISTINCT   LTRIM ( RTRIM (hostname))
    FROM  master..sysprocesses
    WHERE   DATALENGTH ( LTRIM ( RTRIM (hostname)))  >   0

OPEN  loop_name
DECLARE   @host_name   VARCHAR ( 30 )
DECLARE   @exec_str   VARCHAR ( 255 )
FETCH   NEXT   FROM  loop_name  INTO   @host_name
WHILE  ( @@fetch_status   =   0 )
   BEGIN
      SELECT   @exec_str   =   ' master..xp_cmdshell "NET SEND  '   +   @host_name   +    '   '   +   @msg   +   ' " '
      EXEC (  @exec_str )
      FETCH   NEXT   FROM  loop_name  INTO   @host_name
   END
DEALLOCATE  loop_name
GO


/**/ /* Sample Execution:
EXEC sp_dbm_notify_users 'SQL Server will shut down in 30 minutes!'
*/

       说明：执行SQL语句汇出成文件

      SQL：

USE  master
GO
CREATE   PROC  sp_dbm_query_to_file  @db  sysname,  @query   VARCHAR ( 255 ),  @file   VARCHAR ( 255 )  AS
/**/ /*******************************************************************************/
/**/ /* This procedure writes the result from a query to a file.                    */
/**/ /* The procedure takes the following parameters:                               */
/**/ /*   @db SYSNAME (required) : the database where the query will be executed.   */
/**/ /*   @query VARCHAR(255) (required): the query to be executed.                 */
/**/ /*   @file (VARCHAR(255) (required): the filename.                             */
/**/ /* Tested on version 6.5, 7.0 and 8.0.                                         */
/**/ /*******************************************************************************/
SET  NOCOUNT  ON
-- Get version number and verify supported version
DECLARE   @ver   VARCHAR ( 7 )
SELECT   @ver   =   CASE
  WHEN   CHARINDEX ( ' 6.50 ' ,  @@VERSION )  >   0   THEN   ' 6.50 '
  WHEN   CHARINDEX ( ' 7.00 ' ,  @@VERSION )  >   0   THEN   ' 7.00 '
  WHEN   CHARINDEX ( ' 8.00 ' ,  @@VERSION )  >   0   THEN   ' 8.00 '
  ELSE   ' Unknown '
END  
IF   @ver   =   ' Unknown '
BEGIN
    RAISERROR ( ' Unsupported version of SQL Server. ' , 16 , 1 )
    RETURN   - 101
END  

EXEC  ( ' master..xp_cmdshell  '' isql /o '   +   @file   +   '  /d '   +   @db   +   '  /Q" '   +   @query   +   ' " /E ''' )

/**/ /* Sample Execution:
EXEC sp_dbm_query_to_file  'pubs', 'SELECT au_fname FROM authors', 'c: esult.txt'
EXEC sp_dbm_query_to_file  @db = 'pubs', @query = 'SELECT au_fname FROM authors', @file = 'c: esult.txt'
*/

GO

           说明：检查数据库物理文件大小是否超出所选比例

          SQL：

USE  master
GO
CREATE   PROC  sp_dbm_warn_if_full_db  @fillpercentage   DECIMAL ( 5 , 2 )  =   NULL   AS
/**/ /*******************************************************************************/
/**/ /* Generates warning is db is fuller than supplied value.                      */
/**/ /* The procedure does not take logspace into account.                          */
/**/ /* The procedure takes the following parameter:                                */
/**/ /*   @fillpercentage DECMAL(5,2)[NULL]: How full the db should be for a        */
/**/ /*                                      warning to be generated.               */
/**/ /* By specifying 1 as parameter, you get a message each execution.             */
/**/ /* You might want to run this as post-proicessing job, so you get recalc.      */
/**/ /* This proc does not include transaction log space in calculations.           */
/**/ /* Tested on version 6.5, 7.0 and 8.0.                                         */
/**/ /*******************************************************************************/
SET  NOCOUNT  ON
-- Get version number and verify supported version
DECLARE   @ver   VARCHAR ( 7 )
SELECT   @ver   =   CASE
  WHEN   CHARINDEX ( ' 6.50 ' ,  @@VERSION )  >   0   THEN   ' 6.50 '
  WHEN   CHARINDEX ( ' 7.00 ' ,  @@VERSION )  >   0   THEN   ' 7.00 '
  WHEN   CHARINDEX ( ' 8.00 ' ,  @@VERSION )  >   0   THEN   ' 8.00 '
  ELSE   ' Unknown '
END  
IF   @ver   =   ' Unknown '
BEGIN
    RAISERROR ( ' Unsupported version of SQL Server. ' , 16 , 1 )
    RETURN   - 101
END  

-- Declare variables section
DECLARE   @db_full_pages   FLOAT
DECLARE   @db_full_percentage   FLOAT
DECLARE   @msg   VARCHAR ( 255 )
DECLARE   @exec_str   VARCHAR ( 255 )
DECLARE   @db_size   FLOAT
-- Below due to strange behavior for sysfiles table (doesn't act global as other system tables)
CREATE   TABLE  #db_size (db_size  FLOAT )

IF   @fillpercentage   NOT   BETWEEN   1   AND   99   OR   @fillpercentage   IS   NULL
BEGIN
    RAISERROR ( ' Bad parameters: @fillpercentage = How full db for warning to be generated. ' ,  16 ,  1 )
    RETURN   - 101
END  

IF   @ver   =   ' 6.50 '
BEGIN
  --  User defined segments are not supported
  IF  ( SELECT   COUNT ( * )  FROM  master..sysusages  WHERE  segmap  NOT   IN ( 3 ,  4 ,  7 )  AND  dbid  =   DB_ID ())  >   0
    BEGIN
     RAISERROR ( ' User defined segments are not supported. ' ,  16 ,  1 )
     RETURN   - 103
    END

  IF  ( SELECT   COUNT ( * )  FROM  master..sysusages  WHERE  segmap  =   7   AND  dbid  =   DB_ID ())  >   0     --  Data and log not separated
    BEGIN
     SELECT   @db_size   =   SUM (size)  FROM  master..sysusages  WHERE  dbid  =   DB_ID ()
     SELECT   @db_full_pages =   SUM (reserved)  FROM  sysindexes  WHERE  indid  IN ( 0 , 1 )
    END
  ELSE   IF  ( SELECT   COUNT ( * )  FROM  sysusages  WHERE  segmap  IN ( 3 ,  4 ))  >   0     --  Data and log separated
    BEGIN
     SELECT   @db_size   =   SUM (size)  FROM  master..sysusages  WHERE  dbid  =   DB_ID ()  AND  segmap  =   3
     SELECT   @db_full_pages =   SUM (reserved)  FROM  sysindexes  WHERE  indid  IN ( 0 , 1 )  AND  id  !=   OBJECT_ID ( ' syslogs ' )
    END
END
ELSE
BEGIN
     SELECT   @exec_str   =   ' INSERT #db_size SELECT SUM(size) FROM  '   +   DB_NAME ()  +   ' ..sysfiles WHERE status & 0x40 <> 

0x40 '
     EXEC  ( @exec_str )
     SELECT   @db_size   =  db_size  FROM  #db_size
     SELECT   @db_full_pages   =   SUM (reserved)  FROM  sysindexes  WHERE  indid  IN ( 0 , 1 )
END

SELECT   @db_full_percentage   =   @db_full_pages / @db_size * 100
IF   @db_full_percentage   >   @fillpercentage
  BEGIN
   SELECT   @msg   =   ' Database  '   +   DB_NAME ()  +   '  is  '   +   CONVERT ( VARCHAR ( 10 ), CONVERT ( DECIMAL ( 10 , 0 ),  @db_full_percentage )) 

+   '  %% full. '
   RAISERROR ( @msg ,  15 ,  1 )
  END


/**/ /*
Sample execution:
sp_dbm_warn_if_full_db 70
*/

GO