Mini Post: Find All Tables That Have A Given Column

Hey everyone! Welcome back to SQL Something!

Really quick post today. Today we look at finding a table(s) that a column belongs to. Had to find a solution to do this recently and thought it would helpful to share.

The answers we seek can be found in the following SQL Server system views:

• sys.columns
• sys.tables

We will retrieve the related column names from sys.columns and the table names from sys.tables.

Putting it together we get the following:

SELECT       c.name AS ColumnName, t.name AS TableName
FROM          sys.columns c
                     JOIN sys.tables t
                     ON c.object_id = t.object_id
WHERE       c.name LIKE '%YourColumn%'
ORDER BY ColumnName, TableName;

The above will list all tables that have a column name like the given column name ('YourColumn').

And there we go. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Original Reference:  https://stackoverflow.com/questions/26293085/find-all-table-names-with-column-name

Syncing Logins/Users Across SQL Server Instances (For Availability Groups and Mirroring Failovers)

Hey Everyone! Guess who's still around!

Welcome back to SQL Something (with a post dedicated to my friend Greg who guilt-ed me into finally starting back posts). In all seriousness, it's really been far too long.

Today's post briefly touches on SQL Server Always On Availability Groups in SQL Server 2016. You can find a pretty good tutorial on how to set up Always On Availability Groups in an Azure VM via the links here and here. They are pretty thorough (great job team Microsoft!), but they do have a couple very, very small errors/typos that I might point out in a subsequent post.

Today, however, we are merely focusing on a simple login/user issue in order to help streamline failing over to your secondary. The fix is about syncing logins between availability groups. This fix can also be applied to Mirroring (a deprecated feature after 2016)

Azure SQL Database Firewall Issue (Connectivity Issue)

Hey everyone! Yes, I'm still around and yes, this is still SQL Something!

I'm sounding like a broken record now but, as usual, life has been pretty busy. Definitely going to try to finish the year right however, by having at least one post for each of the last couple of months.

Now! Let's get on with it.

Today we are looking at connecting to a Azure SQL Database. In a nutshell, an Azure SQL Database is a DB that exists on a logical DB server, not an actual DB Server VM. It takes away the hassle of having to do server related admin tasks and allows you to focus on the DB itself and the data it contains.

For the purpose of this exercise, let's assume we created a Azure SQL DB and when we attempted to connect to it via Management Studio, we received the following:

Fig. 1: Danger Will Robinson!

Now, we can take the following steps:

• Ensure that you are using the correct server name, username and password.

If the above doesn't work, we will have to verify our Azure SQL Database Firewall rules.

• Log on to portal.azure.com with your valid user.
• Navigate to your azure SQL Databases.
• Click the database in question.
• Select 'Set Server Firewall'.

Fig. 2: Firewall awesomeness.

• Click add Client IP.

Fig. 3: Add yo IP

• Click 'Save'.

Now we can retry our connection.

And that's all there is to it. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: SQL Server Agent Job History

Hey all, welcome back to SQL Something. Man, has it been a while. Too long.

Unfortunate to say however, this post will not be particularly epic to make up for the long absence. Like, not epic at all. But, you know what? Let's go into it. If it helps one person, then that's plenty. :-)

Today we are looking at SQL Server Agent Job history.

A SQL Server Job allows us to automate a process to run at particular times, either as a one time event or repeatedly for some specified duration (or indefinitely). Setting up jobs for performing backups is an example of a great way to use jobs.

A job's history can show us a number of things. It can show us if the job ran successfully and if not, it can give us an indicator as to why. It can also show us some useful info such as the creds used to invoke the job, as well as the duration of time taken for the job to run (this last I find pretty helpful).

SQL Server Fixed Roles And Related Stored Procedures

Good day everyone and welcome back to SQL Something!

Do you like Roles? Do you want to know a little more about SQL Server's built in Roles and what they do? Do you want to know some nifty built in sprocs that also give some extra info about the Roles?

Then this is a post for you!


Firstly let us look briefly at SQL Server's nine (9) built in Roles:

• SysAdmin - This is the big guy. Members of this Role have all the permissions across the instance. A SysAdmin is specified during installation.
• ServerAdmin - Members of this Role are allowed to perform instance configuration tasks and can also stop an instance.
• SecurityAdmin - Members can alter and elevate permissions for Logins. Can elevate permissions to SysAdmin.
• ProcessAdmin - Members can stop instance processes.
• SetupAdmin - Members can add/remove DBs to linked servers.
• BulkAdmin - Members can bulk insert.
• DiskAdmin - Members can perform tasks involving instance related files.
• DBCreator - Members can add/drop/alter/restore databases on an instance.
• Public - The little guy. All logins are considered part of the public Role group. Not a 'real' built in role.

Next, we will examine a couple stored procedures that add to the above info.

Viewing Collation (Server/Database/Column) Via T-SQL

Hey everyone and welcome back to SQL Something!

Real quick one today and that is viewing Collation information via T-SQL (I will do a follow up post eventually on how to do it via Management Studio with pictures).

Now onto the T-SQL.

SSIS Error: Cannot Convert Between Unicode and Non-Unicode Data Types

Hellooo out there! Welcome back to (the not quite monthly) SQL Something!

This month we are looking at a conversion error I ran into when trying to alter an old SSIS package:

Fig. 1: Error!

Fig. 2: Change isn't easy...

Finally an error that's pretty self explanatory! The error says it can't convert from non-Unicode to Unicode, so obviously we need to find some way to make the two the same type. :-)