Bru Medishetty's SeekWell Blogs
Posts tagged T-SQL
DB_NAME() System Function in SQL Server
May 17th
In this blog, let’s take a look at a SQL Server System function, DB_NAME() .
This function is pretty handy when writing scripts that need you to identify the the Database name based on Database ID.
DB_NAME()
DB_NAME() function accepts an optional integer parameter (database_id) and returns the database name of that database_id, if no parameter is mentioned it returns the database name of the current database in whose context the script session is being executed. Let’s look at examples to understand it.
In the following picure, the function DB_NAME() returns the name of the database whose database_id is 1. Since the parameter value is passed on it returns the database name of that database_id, even though the query is running in the database SampleDataBase.
In the next example we look at how this function behaves when the optional parameter is not passed.
The result in this query is the database name in which the query is executed. It returns the database name under whose contect the query is run, when there is a requirement to capture the database name through script, this can be used.
You may also want to take a look at this blog which explains about renaming the Database and the Database objects using T-SQL command. http://learnsqlwithbru.com/2010/03/01/renaming-database-and-database-objects-using-t-sql/
–Bru Medishetty
Find Transaction Log Space Used
Mar 31st
To query the space used by the transaction logs use the command, use DBCC SQLPERF(LOGSPACE). The command displays the Database Name, Log Size in Mega Bytes, the % of log file used, remember the value is not the size it is the percentage used from the actual space of the log files. The results displayed is for all databases on the SQL Server instance.
Find the script below…
DBCC SQLPERF(LOGSPACE)
Monitoring transaction log files for the free space is a one of the very important tasks for a DBA.
– Bru Medishetty
WAITFOR Clause
Mar 28th
When writing T-SQL Statements, the usage of WAITFOR clause will cause a delay in the execution of the subsequent statements until the condition is met.
WAITFOR clause can be used along with Time or Delay. Let us look at them with an example.
WAITFOR DELAY
When WAITFOR DELAY is used a delay duration needs to be mentioned which is of the format HH:MM:SS format followed by one thousandth of a second’s value. In the following image the Delay value mentioned is 5 seconds, hence the execution of the next statement i.e GETDATE() is delayed for 5 seconds. Find the T-SQL code used in the example below…
SELECT GETDATE()
WAITFOR DELAY '00:00:05:000'
SELECT GETDATE()
WAITFOR TIME
When WAITFOR TIME is used, a time value needs to be mentioned which is of the format HH:MM:SS format followed by one thousandth of a second’s value. In the following image the time value mentioned is 09:11:05 AM, hence the execution of the next statement i.e GETDATE() is delayed till that time occurs. Remember that the Time value is a 24 hour format of time. Find the T-SQL code used in the example below…
SELECT GETDATE()
WAITFOR TIME '09:11:05:000'
SELECT GETDATE()
–Bru Medishetty
Recent Comments