Compare Columns Of Two Tables In SQL Server

I was working on a project to check and compare columns of a table with another table to make sure the structure was the same. While we DBAs work regularly on multiple databases, we have a general tendency to use sp_help system procedure or directly use ALT + F1 keys to check the table structure. But if we must check the table structure of multiple tables manually, then such methods should be ruled out.

I have a quick script to help you with comparing columns of two tables using information_schema (System Information Schema Views).

A simple format to compare columns of two tables:

SELECT
db_name() AS DBNAME,
c2.table_name,
c2.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] c2
WHERE table_name = 'table1'
AND c2.COLUMN_NAME NOT IN
( SELECT column_name
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE table_name = 'table2'
)


Example:

In my example, I have created two tables as test1 and test2. 

Table structure - test1  looks like:

Compare Columns Two Tables SQL Server 1


Table structure - test2  looks like:

Compare Columns Two Tables SQL Server 2













Now, let's just the query to check the mismatch between the two tables and its output.

SELECT
db_name() AS DBNAME,
c2.table_name,
c2.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] c2
WHERE table_name = 'test1'
AND c2.COLUMN_NAME NOT IN
( SELECT column_name
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE table_name = 'test2'
)

Compare Columns Two Tables SQL Server 3















The above result gives us the missing column in test2 when compared with the test1 table. 



FlushCache Message In SQL Server

The FlushCache message came into notice for me when my scheduled backup jobs failed due to error - “FlushCache: cleaned up bufs”. FlushCache is the drill and pattern performed by SQL Server for carrying out the checkpoint operation. Additionally, the FlushCache is one such situation that is not experienced frequently by database administrators.

FlushCache Message SQL Server 3

Just imagine if we are trying to fetch a webpage but for some reason, we are unable to fetch the webpage for quite long and get the error as “404 – Page not found”. A similar situation can be compared here, wherein the SQL Server has a regular checkpoint logging. A checkpoint logging happens on the transaction log file that pushes to the hard disc. The basic intent of the checkpoint is to reduce the recovery time in the system failure event.

Further, the FlushCache message is recorded in the SQL Errorlog only while enabling the SQL Server Trace Flag – 3504. The Trace Flag – 3504 is responsible for recording and writing the checkpoint internal activity information in the SQL Server error log.

The Trace Flag – 3504 had to be enabled as a mandate operation prior to SQL Server 2012. The operation can be seen recorded in the error log by default in SQL Server versions after 2012. So, if you are not noticing them without enabling the Trace Flag then here is the guide for you.

You can check the Trace Flag status using the below command:

use <your database name>

dbcc tracestatus(-1)



You can enable the Trace Flag with the command:

dbcc TRACEON( 3504, -1)

Once the Trace Flag is enabled, your SQL system is now ready to record the FlushCache message in the SQL Server error log. Additionally, once the Trace Flag is enabled, you can confirm them by again using my first command – dbcc tracestatus(-1)  

FIX:

So normally saying, your system should be fine but sometimes could cause due to SQL Server overload. Further, most of the cases, the FlushCache erroring resolves by itself. But if you still keep noticing them, then please follow the below commands:

1. Check for all the disk drives and confirm that they are not filled over 85% (Per my assumption). Try to clear some space.
2. 
Run a complete check of the SQL statements running on your system and figure out the query that is spinning the I/O.
3. 
Run a complete throughput of your system performance.

According to the scenarios I have faced, the commands above should settle your system in peace. If you are facing any additional errors along with FlushCache that you are unable to resolve, please send a comment in the comment section and I can immediately respond with possible fixes.

User-Defined Function To Split String In SQL Server

The split string is one required functionality implementation while fledging your application. The user-defined function is useful if you are using a SQL Server machine older than 2016. The main use of the function is to convert a single string of row data into split table values in the form of rows. The string can be delimited either by a comma or any special character in accordance with your data.

Further, if you are using SQL Server version2016 and above, please use the STRING_SPLIT function for better performance. Additionally, if you face any difficulties in using the STRING_SPLIT function that possess error as – 'Invalid Object Name STRING_SPLIT', please refer the details on the link for the fix.

Mostly, we have strings separated by commas in our majority usages. However, we will see the examples of user-defined functions accepting just comma delimiters as well as the ones accepting special characters between the string values.

Split String Function (Accepting the only comma delimiters)

Step 1: Create a function as below:

CREATE FUNCTION Fn_split_string_OnlyComma
(
@in_string VARCHAR(MAX),
@delimiter VARCHAR(1)
)
RETURNS @list TABLE(tab_splitstring VARCHAR(100))
AS
BEGIN
WHILE LEN(@in_string) > 0
BEGIN
INSERT INTO @list(tab_splitstring)
SELECT left(@in_string, charindex(@delimiter, @in_string+',') -1) as tab_splitstring
SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), '')
end
RETURN
END

Step 2: Select from the function – Fn_split_string_OnlyComma

select * from Fn_split_string_OnlyComma ('mango,orange,pineapple',',')

Note: If you are using any special character apart from comma character is going to error out.

Split String Function (Accepting any special character delimiters)

Step 1: Create a function as below:

CREATE FUNCTION [dbo].[fn_Split_String_AnySCharacter]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END

Step 2: Select from the function - fn_Split_String_AnySCharacter

Examples of splitting the string with ‘#’ special character:

select * from [fn_Split_String_AnySCharacter] ('mango#orange#pineapple','#')

User Defined Function Split String SQL Server 2

Examples of splitting the string with ‘?’ special character:

select * from [fn_Split_String_AnySCharacter] ('mango?orange?pineapple','?')

User Defined Function Split String SQL Server 4



Finally, an example of splitting the string with ‘,’ special character:

select * from [fn_Split_String_AnySCharacter] ('mango,orange,pineapple',',')

User Defined Function Split String SQL Server 5



Example of using multiple special characters in the string:

While passing more than one special character in the string, the user-defined function we created is going to accept just the special character we mentioned in the @delimiter parameter. 

select * from [fn_Split_String_AnySCharacter] ('mango,orange%pineapple',',')

User Defined Function Split String SQL Server 6


You'll also like:

"CHOOSE Function"
"Difference Between Clustered And Non-Clustered Index In SQL Server"
"Advantages Of UNION ALL Over Union"

Insert DBCC Results Into A Table In SQL Server

DBAs are very much into using the DBCC commands. The DBCC (Database Console Commands) statements are used for various purposes like maintenance tasks, informational gathering, validation, and miscellaneous tasks. I was working on importing data from one database to another as part of my client’s requirement, and I had to insert the DBCC results into a table.

Most often we as DB professionals tend to just execute the DBCC commands and move on. But there are some instances where we would require the results to be stored into a table. A simple DBCC code can help us fetch the results to resemble that of fetching from a user-table.

Here is how you can insert the DBCC results into a table. Please follow the steps as:

Step 1: Create a temp table as per the DBCC result sets – DBCC SQLPerf(logspace)

While creating the temp table, please make sure that the table definition is matching with the DBCC command’s output as they can end with another error – Number of supplied values doesn’t match the table definition.

Before creating the table definition, please check for the DBCC results. The output number of columns is the one we are focusing on:

EXEC('DBCC SQLPERF(LOGSPACE);')

Insert DBCC Results Into Table SQL Server 1








Once we are clear that there are 4 columns to create from the output in our example here, please proceed to create the table structure as per the instructions below.

create table #Temp_LogSpace
(
[DATABASE_NAME] nvarchar(100),
[LOGSIZE_MB] float,
[LOGSPACE_USED] float,
[LOGSTATUS] bit
)


Step 2: Insert the DBCC  results into temp table (#Temp_LogSpace) using the EXEC ('') command

insert #Temp_LogSpace([DATABASE_NAME], [LOGSIZE_MB], [LOGSPACE_USED], [LOGSTATUS]) 
EXEC('DBCC SQLPERF(LOGSPACE);')

Step 3: Select the data from temp table (#Temp_LogSpace) after insertion

select * from #Temp_LogSpace

Insert DBCC Results Into Table SQL Server



An additional example of DBCC statements into a Temp Table. 

Step 1: Create a temp table as per the DBCC result sets – DBCC Tracestatus()

Please check the results before creating the temp table.

EXEC ('DBCC TRACESTATUS ();') 

Insert DBCC Results Into Table SQL Server







Based on the results, let's create the temp table

Create table #TraceFlag
(
[TraceFlag] varchar(50)
,[Status] bit
,[Global] bit
,[Session] bit
)


Step 2: Insert the DBCC results into a temp table (#TraceFlag) using the EXEC ('') command

insert into #TraceFlag
EXEC ('DBCC TRACESTATUS ();')


Step 3: Select the data from the temp table (#TraceFlag) after insertion

select * from #TraceFlag

Insert DBCC Results Into Table SQL Server





Further, you can as well try to import data from any DBCC statements into a table and check the results. Once you transfer data to a temp table or a user-table, it’s your game to play with the data as per your requirement.

Related interesting topics for you:

"Different between clustered and non-clustered index"
"What is mdf, ndf, ldf"

SP_MSFOREACHDB Stored Procedure To Iterate Through All Databases In SQL Server

The sp_MSforeachdb is a system stored procedure used to iterate through all databases in SQL Server. There are many places where a DBA would need to run a simple SELECT command across all the databases in a server. It is ideally not possible to just waste time on executing database by database manually. The sp_MSforeachdb can simply save our time at just a click of execution on the current instance.

There are two other ways to iterate through all databases, one by using the CURSOR and the other by using the WHILE LOOP. Further, the CURSOR and WHILE LOOP can help us in implementing some iterative functionality that cannot be achieved using the sp_MSforeachdb.

To check the sp_MSforeachdb code for your reference, please execute the code as mentioned below on any of your databases. Basically, they are internally executed from master-level database.

exec sp_helptext sp_MSforeachdb

General Example of sp_MSforeachdb:

declare @cmd varchar(500) 
set @cmd='your command here' 
exec sp_MSforeachdb @cmd

Example of sp_MSforeachdb to PRINT all databases:

Here, the idea is to just print all the databases that are present on your server for information purposes.

declare @cmd varchar(500) 
set @cmd='print "?"' 
exec sp_MSforeachdb @cmd  

sp_MSforeachdb iterate through all DBs SQL Server 1






Example of sp_MSforeachdb to SELECT all databases:

The code on this block is just going to select the list of databases. 

declare @cmd varchar(500) 
set @cmd='SELECT "?" ' 
exec sp_MSforeachdb @cmd

sp_MSforeachdb iterate through all DBs SQL Server 2







Example of sp_MSforeachdb to fetch count of tables from each database:

I am taking an example of selecting the count from sys.tables from each database. Here, I have added an IF condition to exclude the system databases from being iterated.

declare @cmd varchar(500) 
set @cmd='IF "?" not in ("master","model","msdb","tempdb")
BEGIN
USE ?
select DB_Name(),count(1) from sys.tables
END' 
exec sp_MSforeachdb @cmd

sp_MSforeachdb iterate through all DBs SQL Server 3







Example of sp_MSforeachdb to iterate through databases that are part of Availability Group (Primary Server):

One good example of using the sp_MSforeachdb that just iterates through the databases that are included in the primary server’s availability group. 

declare @cmd varchar(500) 
set @cmd='IF "?"  not in
(SELECT A.database_name       
FROM Sys.dm_hadr_database_replica_cluster_states A       
INNER JOIN Sys.dm_hadr_availability_replica_states B on A.replica_id=b.replica_id 
WHERE ISNULL(b.role,3) =1 
AND b.is_local=1 )
BEGIN
USE ?
select DB_Name(),count(1) from sys.tables
END' 
exec sp_MSforeachdb @cmd  

Sp_MSForeachdb iterate through all DBs SQL Server 5

Examination: USE “?” placeholder is one of the most important key while using the sp_MSforeachdb while placing each database before your command respectively. If the placeholder is missed, the stored procedures are just going to iterate the total number of times the databases we have on the server but on the same database, we are currently on. So always be cautiously while using the placeholders.