IF ELSE Control of flow statements

The IF ELSE statements are one of the most frequently used statement within SQL and control of

flow statements are one of the core statements within any programing language. Once you have

understood the concept you can easily make powerful scripts.

An IF statement is a check to see whether a condition is TRUE and is used in hundreds of different

scenarios such as checking whether an object exists, checking data for a value…… I always liken the

IF statement to checking your fridge for your favourite meal and if its there you eat otherwise you

check out all your other options.

The below is a simple script which tests to see whether I have the AdventureWorks2012 database.


IF EXISTS (SELECT * FROM sys.databases

WHERE name = 'AdventureWorks2012' )

PRINT 'AdventureWorks2012 is installed'

The result you will get is below.

AdventureWorks2012 is installed

1

The query checks whether the AdventureWorks2012 returns a value or is TRUE from my query and

then runs the PRINT command to confirm the value was returned as I expected.

You can make the IF statement more powerful by adding the ELSE statement. The ELSE Statement is

used as we often don’t just want to check if a statement is TRUE but also want to produce an action if its false.


DECLARE @TestValue int

SET @TestValue = 1

IF @TestValue >1

BEGIN

PRINT 'The Test value is greater than 1'

END

ELSE

PRINT 'The Test value is less than or equal to 1'

The result you will get is below.

The Test value is less than or equal to 1

2

Change a database state in SQL

Have you ever wanted to change a databases state from read only to read write or vice versa? Well I am going to show you how using some easy to remember T-SQL which you will be using repeatedly over time.

 

We received a database from a client the other day and noticed one of my System Admin was struggling with the restore. He thought he was doing something wrong with the restore and I said he hadn’t done anything wrong and that the database was likely put into that state prior to being backed up and sent to us.

 

I showed him the below T-SQL which enabled him to change the state to read write and then change it to read only if required.

 

Make Database Read Only

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY

GO

 

Make Database Read/Write

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE

GO

 

List SQL databases in SQL Server

Have you ever wanted to list out all the databases you have on a server? Well I am going to show you how to do it quickly and easily.

The other day I had completed a migration from a creaky old dinosaur server to a brand new powerful server which always brings a smile on my face as I like new stuff. I stopped all jobs, put the databases into offline, updated my documentation and diarised a week in the future to finish my clean-up work.

The next day my colleague asked me where I had moved the databases as they did not appear to still be on the server. When he listed the databases using the two following procedures it showed that we were 8 databases down which was the same amount I had put into offline mode.

EXEC sp_databases
EXEC sp_helpdb

I took a step back and did a quick manual search on the server which when you have a few thousand databases can take a while. I found all eight databases and came to the realisation that those two stored procedures can’t find offline databases.

I used the below query to get an accurate list of the databases.

SELECT name
FROM sys.sysdatabases

Find todays date & time in SQL Server part 2

Have you ever wanted to return the time found in GETDATE or CURRENT_TIMESTAMP to a higher level off accuracy in SQL? Well I am going to show you how.

The other day I needed to get the current time to greater accuracy due to the time stamp given to some records and I had to use the following functions which give the time to the millisecond.

SELECT SYSDATETIME ()             —     System time and date

SELECT SYSDATETIMEOFFSET ()       —     The time is included.

SELECT SYSUTCDATETIME ()          —     The date and time is returned as UTC time

1

Have you ever wanted to convert a US date to UK format using SQL?

Have you ever come across the situation where you have data in US format and need it changed to UK format? Well I am going to show you how to use SQL to easily reformat the date.

I have had this problem several times in the past dealing with international clients who supply databases with the incorrect date format and use the below query to change the format.

SELECT CONVERT(VARCHAR(30), CONVERT(DATETIME, ‘6/1/2009 12:00:00 AM‘, 101), 103);

1

Find the current day, month and year in SQL Server

Have you ever wanted to return the value of the current day? Month? Or year? Well its easy in SQL and I am going to show you how.

The query for the current day is below.

SELECT DAY(GETDATE())

The query for the current month is below.

SELECT MONTH(GETDATE())

The query for the current year is below.

SELECT YEAR(GETDATE())

Each query will return a number which will correspond to the information required. For example a value of 2 returned is February in the month query.

1

Find todays date and time in SQL Server

Have you ever wondered how to get the current time and date within SQL Server? Well I am going to show you two easy methods of getting this information.

The first method is my preferred method as it’s slightly shorter than the second method.

SELECT GETDATE()

SELECT CURRENT_TIMESTAMP

1

Find a column in a database with INFORMATION_SCHEMA.COLUMNS

Have you ever just been given a column name and not known which table it was in? Needed to check whether a column existed within a database?…

Well today I was just given a column name and told the information I needed was in there but alas they didn’t know which table it was in.

I could have gone through each table but that is very inefficient and I can more effectively harness the power of SQL to get the answer quickly and easily.

I knew that the INFORMATION_SCHEMA.COLUMNS would be a great place to start and did the below query.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

1

 

The next step was to add the COLUMN_NAME in with a WHERE clause.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME = ‘max_history_rows’

2

SQL JOINS – INNER,OUTER( FULL,LEFT,Right) ,CROSS, SELF

Have you ever been baffled when people talk about SQL joins? Mystified when you hear inner, outer, cross or self? Ran away in fear at the thought of joining two tables?

Well I am about to help you out and face down those fears and explain to you what joins are, the different types and what they are used for.

What is a join?

A join in SQL is a clause which combines two or more tables together within a database. The join is done on matching predicates between two or more tables and the result is a new data set.

Simply this means that it will take two tables and join them on a column within each table which has data that contains similar data.

What is an inner join?

An inner join will return only values where there is match between both corresponding columns in both tables.

Simply it means where the two columns match the rows will be returned in the new table.

What is an outer join? Left? Right? Full?

An outer join is different to an inner join as it does not require the data to match. A Left join would return all record from the left table and only the matching record from the right.

A Right join would return all record from the right table and only the matching record from the left.

A Full outer join returns all unmatched and matched data. Simply it is returning the results of a Left & Right join

What is a self-join?

A self-join is a table which is joined to itself.

What is a cross join

A cross join  is often referred to as a Cartesian product and returns all possible combinations of rows.

How to list all SQL DMVs (Dynamic management views) s and DMFs (Dynamic management functions ) in SQL Server?

Have you ever wondered how many DMVs are in SQL? Wondered whether a DMV exists which help diagnose your problems? Well I am going to show you how to list all the DMVs and DMFs so that you can do a deeper dive into them or await my series where I will be exploring each every DMV and DMF.

Before you run the script it is important to understand that Dynamic Management Views and Functions are used to return information about the status of SQL Server so that you can performance tune issues, check and diagnose for problems.


SELECT * FROM SYS.ALL_OBJECTS

WHERE [name] LIKE '%dm_%'

AND [type] IN ('V', 'TF', 'IF')

ORDER BY [name]

This script will only run in SQL2005 ( SQL 2000 does not include these features)  and above.

In SQL 2008R2 there are 141 DMVs and DMFs and in 2012 there are 171.