Substring and charindex in SQL

No matterProgramIt is still a stored procedure, so we need to develop a good habit of commenting!
Not long-winded. Update a set filename = substring (filename, charindex ('/', filename) + 1, Len (filename ))
Ha ~ Recently, I like databases and regular expressions!

Raiserror

Severity Level of message association defined by the user. You can use a severity level ranging from 0 to 18. The severity levels between 19 and 25 can only beSysAdminUsed by fixed server role members. To use a severity level between 19 and 25, You must select the with log option.

DBCC sqlperfPerf (Active)

Provides statistics on the transaction log space usage of all databases

Syntax: DBCC sqlperf(Logspace)

(Used in the Stored ProcedureExec ('dbcc sqlperf (logspace )'))

The fields contained includeDatabasename,Log Size(MB),Log space used(% ),Status
If the stored procedure is used to check whether the space allocated by the database exceeds a certain value of the allocated space, and back up the database:

Create procedure prbackupiflogalmostfull
-- Do backup of transaction log if percent of space used is bigger then @ fltpercentlimit
(
@ Chvdbname sysname,
@ Fltpercentlimit float,
@ Debug Int = 0
)

As
Set nocount on

Declare @ interrorcode int,
@ Fltpercentused float,
@ Chvdevicename sysname,
@ Chvfilename sysname

Set @ interrorcode =@@ Error

-- How much of log space is used at the moment
If @ interrorcode = 0
Exec @ interrorcode = prlogspacepercentused @ chvdbname, @ fltpercentused output

-- If limit is not reached, just go out
If @ interrorcode = 0 and @ fltpercentused <@ fltpercentlimit
Return 0

If @ interrorcode = 0
Begin
Set @ chvdevicename = @ chvdbname + convert (varchar, getdate (), 112)
Set @ chvfilename = 'C: \ MSSQL7 \ backup \ bkp '+ @ chvdevicename +'. dat'
Set @ interrorcode =@@ Error
End

If @ debug <> 0
Select @ chvdevicename, @ chvfilename

If @ interrorcode = 0
Begin
Exec sp_addumpdevice 'disk', @ chvdevicename, @ chvfilename
Set @ interrorcode =@@ Error
End

-- 15061 it is OK if dump device already exists
If @ interrorcode = 0 or @ fig = 15061
Begin
Backup log @ chvdbname to @ chvdevicename
Set @ interrorcode =@@ Error
End

Return @ interrorcode

Go

Create procedure prlogspacepercentused
-- Return percent of space used in transcation log for specified database
(
@ Chvdbname sysname,
@ Fltpercentused float output
)

As
Set nocount on

Declare @ interrorcode int

Set @ interrorcode =@@ Error

If @ interrorcode = 0
Begin
Create Table # dblogspace
(Dbname sysname,
Logsizeinmb float,
Logpercentused float,
Status int
)
Set @ interrorcode =@@ Error
End

-- Get log space info. For all databases
If @ interrorcode = 0
Begin
Insert into # dblogspace
Exec ('dbcc sqlperf (logspace) ') -- it will not work without Exec
Set @ interrorcode =@@ Error
End

-- Get percent for specified database
If @ interrorcode = 0
Begin
Select @ fltpercentused = logpercentused
From # dblogspace
Where dbname = @ chvdbname

Set @ interrorcode =@@ Error
End

Drop table # dblogspace

Return @ interrorcode

Go

Test the SQL statement running time
In eachSelectStatement Prefix:Declare @ d datetime
Set @ d = getdate ()
AndSelectAdd the following statement:
Select [Statement execution time(Millisecond)] = Datediff (MS, @ D, getdate ())