SQL: List all SQL Server columns and their extended properties

I answered a forum question the other day about how to list all the columns in a SQL Server database. That's straightforward enough, but they also asked for all the extended properties for the column.

In case you need to do this, here's some code:

How it works

I start with the sys.columns view and join it to sys.tables and sys.schemas, to get the schema and table name. The other reason is to make sure it's not a Microsoft-supplied table. I also wanted to exclude the sysdiagrams table that is created when you first create a database diagram using SQL Server Management Studio. (Curiously, that one's not flagged as a Microsoft-supplied table).

The next join was to sys.types to get details of the data type. Notice that the join is on both system_type_id and user_type_id. You need to do that to allow for aliased data types. If you want the underlying base-type, change the ON clause to this:

While the first query would return names like sysname, the alternative ON clause would return same column as the underlying type like nvarchar(128).

Finally, I'm outer applying a query that finds the extended properties for a column. The sys.extended_properties view provides that. It can work with many different types of objects, so you need to closely define what you're looking for. The major_id is the object (i.e. the table) and the minor_id is the column_id. I've used an OUTER APPLY to make sure the column comes back, even if it doesn't have any extended properties.

If you only want columns with extended properties, you could change the OUTER APPLY to a CROSS APPLY instead.

 

 

 

SDU Tools: Dates Between No Weekends

The DatesBetweenNoWeekends function in our free SDU Tools for developers and DBAs, is really popular. It provides a range of dates between starting and ending dates, ignoring weekends.

DatesBetweenNoWeekends is a simple table-valued function that takes two parameters:

@StartDate – the first date to return
@EndDate – the last date to return

The columns returned are:

DateNumber – a sequential number for the dates returned
DateValue – the date

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

 

T-SQL 101: #70 Adding and subtracting intervals in SQL Server T-SQL using DATEADD

When you're writing T-SQL, you might also need to add intervals to dates, perhaps add days or subtract days and so on. The DATEADD() function is the one that we can add or subtract intervals to the date and time.

Now in the example shown here, what I've said is in 20190228 or  28th of February 2019. I want to add on 12 days. To subtract 12 days, I would have just put -12 instead of 12. You can see the output here:

Now there are a wide variety of intervals. It's not just days. There are values right down to nanosecond.

Notice also though, that there are abbreviations for each of these. So instead of writing day, you could just write d. Or you could write dd, but I'd suggest you don't do that.

You might imagine that minute is m. But m is actually month and so on, so it can get very confusing. For someone who's reading your code, it's much, much better if you actually use the full word. So even words like nanosecond, second or millisecond, just put the whole word.

You'll see lots of old programming where people have used the abbreviation instead. Don't follow that.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SQL: What is the difference between connecting to .\SQLEXPRESS and (localdb)\v11.0 ?

I keep hearing questions from developers about the difference between SQL Server Express Edition and LocalDB. One asked me the other day:

What's the difference between connecting to .\SQLEXPRESS and (localdb)\v11.0 ?

SQL Express (it's really SQL Server Express Edition) is a service-based version of SQL Server i.e. it runs as a service all the time, independently of other applications. When you say .\SQLEXPRESS you are looking for a named instance of SQL Server called "SQLEXPRESS" that is on your local machine and connected to via a shared memory interface (that's what the dot is).

Local DB is a deployment option for SQL Express that runs as an attached process to another application, instead of as a service. It makes it easier to deploy applications like Visual Studio that need a copy of SQL Server locally but don't want to be heavy-handed by installing a service as well as the IDE. You can read about LocalDB here.

Visual Studio also just needs to use a SQL Server for a while during development, but doesn't need it running all the time. When you connect to "(localdb)\v11.0" you are connecting to one of these attached instances, and in this case using version 11. When you start it by accessing it from within Visual Studio, it shuts down again when you close Visual Studio.

It doesn't have to be attached to Visual Studio though. You could attach it to your own application. You can also have multiple versions of localdb on the same machine.

You can even start and stop Local DB separately from applications like Visual Studio. The SqlLocalDB Utility can be used to manage the lifetime (starting and stopping) of Local DB. You can read about it and the functions that it offers, here.

SQL: Adding time to Change Data Capture (CDC) Events

Several times now on forums, I've seen questions about Change Data Capture (CDC). People like the way they can retrieve details about changes that have occurred (often to trickle-feed into a data warehouse), but they are puzzled why CDC doesn't tell them when the event occurred. There's an easy fix for that.

Let's start by doing a quick CDC setup:

And let's then add three rows of data, then change one of them:

There are two ways we can see the changes:

That returns the following:

The first SELECT returns all changes as they occur, and the second SELECT returns the net affect of those changes. Because NewEmployeeID #1 was inserted as Fred Jones, and later changed to Greg Low, the net affect is just of an insert as Greg Low.

But notice, there's no time value. What we can use to get the time is the LSN (log sequence number) by calling the sys.fn_cdc_map_lsn_to_time() function like this:

Now that returns:

You can see that the function returns us the time. The time is in local time for the server.

If you want to tear the example back down, just do this:

 

SDU Tools: Date Dimension Period Columns in SQL Server T-SQL

The DateDimensionColumns function in our free SDU Tools for developers and DBAs, has been really popular. It provides the standard columns that are part of a typical date dimension in a data warehouse. But we're especially proud of a new function that can be used to expand a date dimension with really useful additional information. We call it DateDimensionPeriodColumns.

The DateDimensionColumns function had the usual columns:

Date, Day Number, Day Name, Short Day Name, Month Name, Short Month Name, Month Number, Month Label, Year, Year Label, Day of Year, Fiscal Month Number, Fiscal Month Label, Fiscal Year, Fiscal Year Label, Day of Fiscal Year, ISO Week Number

But the DateDimensionPeriodColumns function adds many more useful details:

Is Today, Is Yesterday, Is Tomorrow, Is Future, Is Working Day, Is Last Working Day, Is Next Working Day, Is Weekend, Is Same Month, Is Month to Date, Is Same Month Last Year, Is Month to Date Last Year, Is Same Calendar Year, Is Calendar Year to Date, Is Last Calendar Year, Is Last Calendar Year to Date, Is Same Fiscal Year, Is Same Fiscal Year to Date, Is Last Fiscal Year, Is Last Fiscal Year to Date

That should let you say bye-bye to a whole lot of tricky DAX; it's easy to just use those columns for relative or period filtering

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

 

T-SQL 101: #69 Extracting date components in SQL Server T-SQL with DAY, MONTH, and YEAR

When you're writing T-SQL code, it's really common to need to extract components of a date, and the most common need is for year, month, and day. So T-SQL has separate functions just for that.

You can see the output from this query here:

The YEAR(), MONTH(), and DAY() functions take a date (or datetime, datetime2, smalldatetime) as input and return an integer for the year, month, or day.

You can also do this with the DATEPART() function but these are simpler and clearer when you want these main components of a date.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SQL: How do I stop my database files going into the Users folder with localdb?

I've seen questions on the forums asking about where localdb stores its database files. Often, the question is about how to stop them going into the C:\Users folder. Clearly, that doesn't seem to be a good spot for them.

localdb stores database files in the C:\Users area by default as it's trying to find a location that the current user will have permission to write to. While I understand the rationale, that's not where most users will want those files.

The answer is to not let localdb pick where the files will go.

Instead of writing:

use a more complete command like:

While you're at it, check out the CREATE DATABASE syntax and examples for other options that might be useful to you, particularly the ones around filegrowth. (Hint: Use a fixed increment, not a percentage) Not everything will work on localdb but most will.

 

SQL: Why didn't my SQL Server transaction roll back on a primary key violation?

There's a question that I hear all the time in SQL Server forums:
 
Why didn't the transaction roll back when a primary key violation occurred?
Take a look at the code in the main image above. Would you expect it to execute the second and third INSERTs if the first INSERT failed with a primary key violation? So many people would. If you're one of them, read on.
 
By default, that's not how SQL Server works.
 
It might not be a primary key violation. It might be any number of other errors in the question, but the answer is still the same.
 

What's going on?

The primary key violation is an example of what's called a statement-terminating error. T-SQL data modification statements are atomic. If the INSERT was inserting three rows, none get inserted.
 
But only the statement ends, not the transaction.
 
If, instead, the error was a batch-terminating error, there's a difference. SQL Server aborts the transaction and rolls it back.
 

How can I change that?

If you want to change that behaviour, put
 

at the start of your code.

It tells SQL Server to promote statement-terminating errors to batch-terminating errors. Once you do that, the transaction code above works as expected. The primary key violation aborts and rolls back the transaction.

But I've seen it work without that!

I also occasionally hear people say that they've seen a different outcome. They didn't use XACT_ABORT and it still rolled back. What they're usually missing is that their data access library has set it on for them.
You can tell if it's on by executing this code:

Note: there are a few edge cases on this. Errors that relate to invalid object names, etc. are different. But for standard code, this is how it all works.

SDU Tools: Extracting initials from a name in SQL Server T-SQL

I recently came across a requirement to extract someone's initials from within their name. That was a new one for me, so we added a new function into our free SDU Tools for developers and DBAs. It's called InitialsFromName.

It's a straightforward scalar function that takes two parameters:

@Name nvarchar(max) is the name to extract the initials from

@Separator nvarchar(max) is a separator placed between the returned initials.  (Make it an empty string for none)

In the main image above, you can see the difference between the first two examples. The only difference is the separator.

You can use our tools as a complete set or as a library that provides great examples of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.