Mini Post: Listing ALL Databases On A Server

Heyyyy everyone, Geon still here. I hope everyone has been well during these times and keeping safe. Remember to wash your hands and wear your mask!

Now, I realized I made a few listing queries over the years: how to list all stored procedures, how to list all tables with a given name, how to list all connections... but I just realized I never did the simplest of them all:

How to list all databases on a server.

To do this, we can query the sys.databases system table. This table can provide us with a wealth of database knowledge such as:

• Database Name
• Database ID
• Creation Date
• State
• Collation

You can filter on any of these fields as you would a regular query.

Please see below for a simple example query I've had to use recently:

SELECT 
s.name as DatabaseName,
s.database_id as DatabaseID,
s.create_date as CreationDate,
s.collation_name as Collation,
s.recovery_model as RecoveryModel,
s.state_desc
FROM 
sys.databases s
WHERE
create_date >= '01/01/2020'
ORDER BY name;

I hope this was useful to someone. :-)


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. :-)

Using LAG To Find The Difference In Timestamps Between Rows

Hey everyone, tis another merry entry of SQL Something!

Slipping in this last post for 2019!

HAPPY NEW YEAR AND ALL THE BEST FOR THE UPCOMING 2020!

Today we are going to look at using LAG and specifically how to use it to find the difference between times, where one time value... IS IN THE PREVIOUS ROW.

Turns out it's really simple using LAG (available in SQL 2015 onwards).

Dropping Multiple Tables That Include A Specific String In Their Names

Good day everyone!

It is I, Geon Bell, back with another SQL Something! (Still alive!)

Today we are going to look at something questionably dangerous: Dropping Multiple Tables at Once Via One Query!

Yeah, I'm shocked at myself for even suggesting that.

Not gonna lie, I still get very... anxious when performing table drops, much less doing them en masse, but it'll be fiiiine. Maybe.

On a serious note, my scenario was as follows: I had come across a situation where I restored a Azure SQL bacpac on my local machine, in order to fiddle around with it safely. The database, however, was part of an Azure DataSync and came with a bunch of tables I didn't need. Thankfully they all ended with the same suffix.

I didn't want to drop each table individually, so I went looking across the internet for an easier way and found this link: https://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string

Thank you very much to Curt and Filip!

Now the query they gave was as follows:

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

When I ran it (with my appropriate suffix), I got the error: "Cannot drop table MyTableName because it does not exist or you do not have permission." for each table.

Checking the online again, I saw that people removed the '[' brackets from their regular drop statements to solve that issue. It still didn't work, so I also added the schema name as well.

I then combined the three solutions: The original query, AND I removed the squared brackets AND added the schema name. The final working solution for me was as follows:

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table SchemaName.' + Table_Name --Add schema name, remove brackets
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

And that's that. :-)

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: Detecting Orphaned Users; sp_change_users_logins

Good day all! We are all the way in October 2018 and we are due for another SQL Something! Man is the year flying by!

Today, we are looking at detecting orphaned users. In short, these are usually users that do not have an associated SQL login.

You can detect these 'orphans' using the sp_change_users_login stored procedure with the @Action='Report' option.

Mini Post: DBCC SHRINKFILE Error: Msg 8985, Could not locate file in sys.database_files.

*Slides by*

Hey everyone, sliding in a post on the last day of the month of August. I already missed July :'-(

Anyways, today we will be looking at the following error(s) I got when attempting to shrink a database file via DBCC SHRINKFILE:

• Msg 8985, Level 16, State 1, Line #
  Could not locate file 'FileName_1' for database 'DatabaseName' in sys.database_files. The file either does not exist, or was dropped.
• 
  Msg 5041, Level 16, State 1, Line #
  MODIFY FILE failed. File 'FileName_1' does not exist.

These errors have to do with incorrect filenames you might be using for the DBCC SHRINKFILE parameters. And by incorrect, I mean it is possible that you, like me, were not using the logical filename.

One way to double check the logical filename would be to run the following on the database in question to get the info from sys.database_files:

SELECT 
   file_id AS FileID,
   name AS LogicalFileName, 
   size/128.0 AS FileSizeMB,
   size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
                   AS EmptySpaceMB
FROM 
   sys.database_files;

The query gives you some additional information such as file_id (which you can use instead of the logical name) as well as file size and 'empty' space to better determine your file reduction planning.

Hope this helps. :-)

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: 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

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.

Using The LEAD() And/Or LAG() Functions

Heyyy, sliding in at the eleventh hour (as usual) we have the first SQL Something for 2016!
Hard to believe that I've actually managed to, sort of, maintain this for 3 years.

Anyhow, we end the year with a couple functions that follow a similar format to RANK(): LEAD()/LAG().

LEAD() and LAG() allow users to list the value of the next row or previous row respectively with some given offset from the current row.

Simple example time.

Using The RANK() Function

Hey everyone! Welcome to (or back to) SQL Something!

Quick filler for this month: Using The RANK() Function.

I will demonstrate this function using a very simplistic example.

Firstly let's look at a table I created called [Files] that consists of the below dummy data:

Fig. 1: Important data...

Mini Post: The data types ntext and varchar are incompatible in the equal to operator.

Hello and welcome back to SQL Something!

It's Geon coming in at the eleventh hour to drop a post before the month is up.

Today we are going to look at the following error(s):

Msg 402, Level 16, State 1, Line <number>
The data types ntext and varchar are incompatible in the equal to operator.

OR

Msg 306, Level 16, State 2, Line <number>
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Fig. 1: #ntextProblems

As the text is basically saying, these errors occur when you try to use an equal operator ("=") to compare a varchar value to a ntext value.
So what can you use to compare? The answer is right in the second error. You can use a LIKE.


See the example below:

...WHERE ntextValue LIKE 'SomeTextValue'...

Simple enough. :-)



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. :-)

List Multiple Row Values on Single Row (Comma Separated List)

Hey every and any body! Welcome back (or welcome to) SQL Something!

The main post for this month is looking at how to create a comma separated list for a one to many relationship (found either within the same table or between two tables).

Let's look at the following. Suppose I have the following values in a table:

Fig. 1: Transactions and their Messages...

You can see there exists a one to many relationship between this table (Table1) and another table (Table2) where multiple messages in Table1 correspond to single transaction in Table 2.

Suppose we wanted to list only the transactions and their corresponding messages. We can list the results as follows:

Fig. 2: List of Transactions and their Messages...

But suppose we want to list all the corresponding messages in one field as follows:

Single row list of Transactions and their Messages...

To achieve that we'll have to use FOR XML, specifically the FOR XML PATH variant. We can then use the below query:

--------------------------------------------------------------------------------------

SELECT T1.Tran_ID,
        (   SELECT       T2.Message + ','
            FROM          [Table1] T2
            WHERE       T2.Tran_ID = T1.Tran_ID
            ORDER BY T2.Message
            FOR XML PATH('')
        ) AS Transactions
FROM [Table1] T1
GROUP BY Tran_ID;

--------------------------------------------------------------------------------------

What this query does is reference itself with a subquery for each instance of a Tran_ID. For each instance of a Tran_ID, it looks for the corresponding messages and, using FOR XML PATH(''), it concatenates them with a comma separator.

By providing a 'blank' hierarchy in the FOR XML PATH clause, SQL does not create any element tabs and so we get one continuous string. A very good read up on the various FOR XML variations can be found here.

And that's 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. :-)

How to Get the Last (Latest/Newest) Record in a One-to-Many Relationship/Join

Good day everyone and welcome back to SQL Something!

Today we're looking at an issue that was presented to me today: How to get the last record in a one to many relationship.

Some digging online found this original post here, which I then had to deconstruct to understand it. To that end, I made a couple test tables and then went through the query, while making small modifications to suit my needs.

Let's go through it.

Mini Post: List All Stored Procedures/Functions In A Database (Information_Schema.Routines)

Hey all! Welcome to, or welcome back, to SQL Something!

Today we're taking a quick look at how to list all Stored Procedures (and Functions) in a database. As per usual, I needed to do this at some point for my job and so I'm sharing what I found. :-)

In order to get the information we need we will be querying information_schema.routines as follows:

SELECT    ROUTINE_NAME as 'Stored Procedure',
        ROUTINE_CATALOG as 'Database Name',
        ROUTINE_SCHEMA as 'Database Schema',
        ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
        ROUTINE_DEFINITION as 'Stored Procedure Definition',
        CREATED as 'Date Created',
        LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME

The above will list all Store Procedures for the database that you run the query on. Very helpful! :-)


Now you'll notice the WHERE clause specifies the PROCEDURE type only. If we wanted info on Functions as well as Stored Procedures, we can remove this clause.


If we wanted info on Functions only and wanted some additional info like what value the Function returns, we tweak the query as follows:

SELECT    ROUTINE_NAME as 'Function Name',
        ROUTINE_CATALOG as 'Database Name',
        ROUTINE_SCHEMA as 'Database Schema',
        ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
        ROUTINE_DEFINITION as 'Function Definition',
        DATA_TYPE as 'Return Value Type',
        CREATED as 'Date Created',
        LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

DATA_TYPE returns the data type of the Function's return value or it returns TABLE if it's a table valued function.


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: Each GROUP BY expression must contain at least one column that is not an outer reference

Good day and welcome back to SQL Something!

I'm ashamed at how long I've been away and there is really no excuse. It is, however, a new year (2015 people!), so I'm going to try to be much better. Here's hoping!

We'll start this year with a very simple error that you may see when running a query:

"Each GROUP BY expression must contain at least one column that is not an outer reference"

The error itself is a bit vague, but refers to something quite simple. As the above error indicates, a "Group By" is involved. Maybe your query involves a variable like so:

SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2], @Variable;

Do you know what's wrong? It's the variable in the GROUP BY clause.

Normally, when using GROUP BY, you are required to put all the columns in the SELECT clause that are not part of a aggregate function into the GROUP BY clause. Following that logic you should also put any local variables that you are using in the SELECT clause. This however is not so.

Removing the variable like so fixes the query:

SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2];

Thank you, as usual, for your time. :-)


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. :-)

Error: The Server Instance Witness Rejected Configure Request

Hey everyone out there! Welcome back to SQL Something!

Today we look at an interesting problem. I'm not sure as to the root cause of it, but we will look at how to solve it.

(P.S. If anyone out there knows why this occurred, please feel free to contribute in the comments below)

The error in question is:

The server instance Witness rejected configure request; read its error log file for more information. The reason 1427, and state 31, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

Fig. 1: So it begins.

I discovered this error repeating continuously in the Application Log in the Windows Event Viewer.

Naturally I checked all the databases currently being mirrored, however all seemed to be in order (both the Principals as well as the Mirrors). So how can we find out more about the issue and how can we fix it?

Mini Post: Showing Permissions Granted To A User, Via T-SQL

Hey everyone! Welcome back to SQL Something!

Today we are going to take a quick look at how to view the available permissions a user was granted. I needed this the other day when I wanted to create a user with similar permissions for another DB. Luckily fn_my_permissions has all the answers we need.

We can query fn_my_permissions to find out permissions info at a DB level like so:

USE YourDBName;
SELECT *
FROM fn_my_permissions (NULL, 'DATABASE');

This, however, will list the permissions for the current user doing the query (which might not actually be the user you want the info for).

To find permissions for a specific user, you must first impersonate that user like so:

USE YourDBName;
EXECUTE AS USER 'User1';
SELECT *
FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;

Please note that using the EXECUTE command will give you only the permissions of the user you are impersonating. You need the REVERT command at the end of the query to give yourself back the permissions you previously had.

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. :-)

Getting Database Creation Date Via T-SQL

Hey everyone! Welcome back to SQL Something!

The first post for April is going to be pretty simple. Today we are going to look at how to retrieve the date that a database was created via T-SQL. Could be useful in some situations. I, for instance, just wanted to know when a DB was swapped out for a empty one. The new empty DB wasn't put to use the same day/time it was created, so the timestamps of the actual data in the DB didn't exactly provide me with what I wanted.

Anyway, I digress. Let's look at what sys.databases can do in order to help with our problem.

Mini Post: Getting A List of Connections By IP Address

Hello and welcome back to SQL Something!

I was looking at how to view current connections the other day (much like this past blog post) when I think I saw someone mention checking SQL connections by IP address. I thought this would be pretty cool if it was possible, and after some light searching I found this nice, succinct post by Glenn Berry.

I won't post the query here, but suffice to say it does the job very well.

It uses fields found in the sys.dm_exec_connections and sys.dm_exec_sessions views in order to display the client_net_address (the IP address), the host_name and the login_name as well as a count of session_id per login which provides a connection count per login. Awesome sauce.

Finally, there is a second query in the original post by Mr. Berry that gives you strictly a count by login name using only the sys.dm_exec_connections, which is neat (though honestly I'm not too sure how useful; If you can think of a great way the second query can be used, please let me know by commenting).

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: Group By Month (or Year)

Hello to all of you out there! Welcome back to SQL Something!

Today's Mini Post is showing how to group by a date part (such as month, year) when given a start and end date.

Today specifically we are going to group by month in the following example:

SELECT DATENAME(month, YourDateField) MonthName,
                DATEPART(month, YourDateField) MonthNumber,
                COUNT (YourField) FieldTotal
FROM YourTable
WHERE (YourDateField >= '01/01/2013' and YourDateField < '01/01/2014')
 -- AND add other criteria here
GROUP BY DATENAME(month, YourDateField),
                     DATEPART(month, YourDateField)
ORDER BY MonthNumber;

And that's all there is too 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. :-)