非常有用的一些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
扫一扫
1052
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
