CSDN博客

img tdl982324

一些比较有用的实例应用SQL(For DBA)

发表于2004/10/21 10:33:00  1177人阅读

非常有用的一些SQL语句:

1.数据库字段列表
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


2.检查数据库物理文件大小增长
--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

3.Kill All Connecting Process
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'
*/

4.发送用户信息
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!'
*/


5.执行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:/result.txt'
EXEC sp_dbm_query_to_file  @db = 'pubs', @query = 'SELECT au_fname FROM authors', @file = 'c:/result.txt'
*/

GO


6.检查数据库物理文件大小是否超出所选比例
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

阅读全文
0 0

相关文章推荐

img
取 消
img