SQL SERVER – Finding Different ColumnName From Almost Identitical Tables

I have mentioned earlier on this blog that I love social media – Facebook and Twitter. I receive so many interesting questions that sometimes I wonder how come I never faced them in my real life scenario. Well, let us see one of the similar situation. Here is one of the questions which I received on my social media handle.

“Pinal,

I have a large database. I did not develop this database but I have inherited this database. In our database we have many tables but all the tables are in pairs. We have one archive table and one current table. Now here is interesting situation. For a while due to some reason our organization has stopped paying attention to archive data. We did not archive anything for a while. If this was not enough we  even changed the schema of current table but did not change the corresponding archive table. This is now becoming a huge huge problem. We know for sure that in current table we have added few column but we do not know which ones.

Is there any way we can figure out what are the new column added in the current table and does not exist in the archive tables? We cannot use any third party tool. Would you please guide us?”

Well here is the interesting example of how we can use sys.column catalogue views and get the details of the newly added column. I have previously written about EXCEPT over here which is very similar to MINUS of Oracle.

In following example we are going to create two tables. One of the tables has extra column. In our resultset we will get the name of the extra column as we are comparing the catalogue view of the column name.

USE AdventureWorks2012
GO
CREATE TABLE ArchiveTable (ID INT, Col1 VARCHAR(10), Col2 VARCHAR(100), Col3 VARCHAR(100));
CREATE TABLE CurrentTable (ID INT, Col1 VARCHAR(10), Col2 VARCHAR(100), Col3 VARCHAR(100), ExtraCol INT);
GO
-- Columns in ArchiveTable but not in CurrentTable
SELECT name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'ArchiveTable'
EXCEPT
SELECT
name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'CurrentTable'
GO
-- Columns in CurrentTable but not in ArchiveTable
SELECT name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'CurrentTable'
EXCEPT
SELECT
name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'ArchiveTable'
GO
DROP TABLE ArchiveTable;
DROP TABLE CurrentTable;
GO

The above query will return us following result.

I hope this solves the problems. It is not the most elegant solution ever possible but it works. Here is the puzzle back to you – what native T-SQL solution would you have provided in this situation?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – 2012 – List All The Column With Specific Data Types in Database

5 years ago I wrote script SQL SERVER – 2005 – List All The Column With Specific Data Types, when I read it again, it is very much relevant and I liked it. This is one of the script which every developer would like to keep it handy. I have upgraded the script bit more. I have included few additional information which I believe I should have added from the beginning. It is difficult to visualize the final script when we are writing it first time. I use every script which I write on this blog, the matter of the fact, I write only those scripts here which I was using at that time. It is quite possible that as time passes by my needs are changing and I change my script. Here is the updated script of this subject. If there are any user data types, it will list the same as well.

SELECT s.name AS 'schema', ts.name AS TableName,
c.name AS column_name, c.column_id,
SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
t.name AS Datatypename
,t.is_user_defined, t.is_assembly_type
,c.is_nullable, c.max_length, c.PRECISION,
c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
ORDER BY s.name, ts.name, c.column_id

I would be very interested to see your script which lists all the columns of the database with data types. If I am missing something in my script, I will modify it based on your comment. This way this page will be a good bookmark for the future for all of us.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Validating Unique Columnname Across Whole Database

I sometimes come across very strange requirements and often I do not receive a proper explanation of the same. Here is the one of those examples.

Asker: “Our business requirement is when we add new column we want it unique across current database.”

Pinal: “Why do you have such requirement?”

Asker: “Do you know the solution?”

Pinal: “Sure I can come up with the answer but it will help me to come up with an optimal answer if I know the business need.”

Asker: “Thanks – what will be the answer in that case.”

Pinal: “Honestly I am just curious about the reason why you need your column name to be unique across database.”

(Silence)

Pinal: “Alright – here is the answer – I guess you do not want to tell me reason.”

Option 1: Check if Column Exists in Current Database

IF EXISTS
(  
SELECT *
FROM sys.columns
WHERE Name = N'NameofColumn')
BEGIN
SELECT
'Column Exists'
-- add other logic
END
ELSE
BEGIN
SELECT
'Column Does NOT Exists'
-- add other logic
END

Option 2: Check if Column Exists in Current Database in Specific Table

IF EXISTS
(  
SELECT *
FROM sys.columns
WHERE Name = N'NameofColumn'
AND OBJECT_ID = OBJECT_ID(N'tableName'))
BEGIN
SELECT
'Column Exists'
-- add other logic
END
ELSE
BEGIN
SELECT
'Column Does NOT Exists'
-- add other logic
END

I guess user did not want to share the reason why he had a unique requirement of having column name unique across databases. Here is my question back to you – have you faced a similar situation ever where you needed unique column name across a database. If not, can you guess what could be the reason for this kind of requirement? 

Additional Reference:
SQL SERVER – Query to Find Column From All Tables of Database

Click to Download Scripts

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1

I personally use the sys schema and DMV to retrieve most of the information. However, I am not surprised see usage of Information_Schema. It has been very popular and works in most of the time. Though, I do not use any feature it does not mean everybody else should stop using the same feature. The matter of the fact, when I receive questions about features which I have not used frequently I feel refreshed to come across new concepts.

Just a few days ago, I received a simple question about INFORMATION_SCHEMA.COLUMNS table. The question was as follows:

Question: I often see the value -1 in the CHARACTER_MAXIMUM_LENGTH column of INFORMATION_SCHEMA.COLUMNS table. I understand that the length of any column can be between 0 to large number but I do not get it when I see value in negative (i.e. -1). Any insight on this subject?

Answer: Of course, I love this kind of simple question which often know the answer or assume that we know the answer. Whenever we use data type VARCHAR(MAX) for any column it is represented by -1 in INFORMATION_SCHEMA.COLUMNS table. Let us see a quick demonstration of the same.

Let us create a table which has column which is of VARCHAR(MAX) and see the result returned by the same.

-- Create Sample Table
CREATE TABLE t(id INT,name VARCHAR(200),address VARCHAR(MAX))
GO
-- select from columns
SELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=OBJECT_NAME(OBJECT_ID('t'))
GO
-- drop table
DROP TABLE t
GO  

Let us check the resultset.

You will see that the column address which is of datatype VARCHAR(MAX) have Character Maximum Length value as -1. You will see the same behavior from nvarchar(max) and varbinary(max).

I personally believe in simple learning – if we learn a thing a day we will learn 365 new things every year!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Get Schema Name from Object ID using OBJECT_SCHEMA_NAME

Sometime a simple solution have even simpler solutions but we often do not practice it as we do not see value in it or find it useful. Well, today’s blog post is also about something which I have seen not practiced much in codes. We are so much comfortable with alternative usage that we do not feel like switching how we query the data.

I was going over forums and I noticed that at one place user has used following code to get Schema Name from ObjectID.

USE AdventureWorks2012
GO
SELECT s.name AS SchemaName, t.name AS TableName,
s.schema_id, t.OBJECT_ID
FROM sys.Tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = OBJECT_NAME(46623209)
GO

Before I continue let me say I do not see anything wrong with this script. It is just fine and one of the way to get SchemaName from Object ID.

However, I have been using function OBJECT_SCHEMA_NAME to get the schema name. If I have to write the same code from the beginning I would have written the same code as following.

SELECT OBJECT_SCHEMA_NAME(46623209) AS SchemaName, t.name AS TableName,
t.schema_id, t.OBJECT_ID
FROM sys.tables t
WHERE t.name = OBJECT_NAME(46623209)
GO

Now, both of the above code give you exact same result. If you remove the WHERE condition it will give you information of all the tables of the database.

Now the question is which one is better – honestly – it is not about one is better than other. Use the one which you prefer to use. I prefer to use second one as it requires less typing. Let me ask you the same question to you – which method to get schema name do yo use? and Why?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Resource Database ID – 32767

Earlier I blogged about SQL SERVER – What Kind of Lock WITH (NOLOCK) Hint Takes on Object?. After reading the post, I got question by one of the blog reader.

“Hi Pinal,

I see in your blog post you have Database ID which is 32767. Everytime I want to get the name of the database from database_ID I use following function but this time this function returned NULL.

SELECT DB_NAME(32767)

When I tried to list all the databases uses following script it did not have that database ID as well.

SELECT *
FROM sys.databases

I assume you have created this many database is that true?”

Very interesting question to me. I have never thought about it when I posted as I assumed few things. Here is the answer. Database_ID 32767 is reserved Resource Database.I have not created that many databases. This database is hidden from users in SSMS but you can see that if you go to file folder. You can read more about the same over here SQL SERVER – Location of Resource Database in SQL Server Editions.

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. (from BOL).

In SQL Server maximum databases per instances can be created are 32,767. This last number has been reserved by Resource Database itself.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – ObjectID in Negative – Local TempTable has Negative ObjectID

I used to run the following script to generate random large results. However, when I ran this on Denali I noticed a very interesting behavior:

SELECT o1.OBJECT_ID,o1.name, o2.OBJECT_ID, o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2

I noticed lots of negative object_ID’s on Denali, whereas my experience on SQL Server 2008 R2 as well as the earlier versions was it was always giving me a positive number.

This whole thing interested me so I decided to find out objects which belonged to the negative object_ID. When I looked at the name of the object, it was very evident that it belonged to TempTable.

To verify my finding, I ran the following command for a couple of times:

CREATE TABLE #LocalTempTable (ID INT)

Then, I realized that every time I ran this command, I found one more negative ID added in the objects table. To further clarify this, I ran the following command:

SELECT *
FROM tempdb.sys.tables

This command really validated the event that whenever LocalTempTable is created in the Denali, it creates object_ID with negative number. However, in SQL Server 2008 R2 and earlier versions, it is always a positive number.

I attempted to create Global TempTable using the following code. The result was all of the global TempTable created Object_ID’s which yield positive numbers. For the moment, it looks like all the LocalTempTable have negative ID’s.

CREATE TABLE ##GlobalTempTable (ID INT)

Let me ask you a few questions:

  • Are there any other objects in SQL Server ‘Denali’ that have negative object_ID?
  • What can be the reason behind  the negative object_ID?

On a separate note, take a look at the following code:

IF OBJECT_ID('tempdb.dbo.#LocalTempTable') > 0

If you updated to Denali you should change it to either
IF
OBJECT_ID('tempdb.dbo.#LocalTempTable') <>0

or
IF OBJECT_ID('tempdb.dbo.#LocalTempTable') IS NOT NULL

Reference: Pinal Dave (http://blog.SQLAuthority.com)