T-SQL Bites

Check if a table with the specified name exists or not.

select obj.*
from sys.objects as obj
join sys.schemas as sch on sch.schema_id = obj.schema_id
where OBJECTPROPERTY(obj.object_id, 'IsTable') = 1 -- obj.type = 'U'
and sch.name = '<schema_name>'
and obj.name = '<table_name>'

You can use the OBJECTPROPERTY function to resolve other type of objects in the database.

Check if a foreign key between two tables with a specified name exists or not.

select fk.* from sys.foreign_keys fk
join sys.schemas as sch on sch.schema_id = fk.schema_id
join sys.objects as parentobj on parentobj.object_id = fk.parent_object_id and parentobj.schema_id = sch.schema_id
join sys.objects as refobj on refobj.object_id = fk.referenced_object_id and refobj.schema_id = sch.schema_id
where sch.name = '<schema_name>'
and fk.name = '<foreign_key_name>'
and parentobj.name = '<foreign_key_table_name>'
and refobj.name = '<referenced_table_name>'

Check if an index with a specified name exists on a table or not.

select *
from sys.indexes as idx
join sys.objects as obj on obj.object_id = idx.object_id
join sys.schemas as sch on sch.schema_id = obj.schema_id
where idx.name = '<index_name>'
and obj.name = '<table_name>'
and sch.name = '<schema_name>'
-- and idx.is_primary_key = 1
-- and idx.is_unique_constraint = 1

 

R.I.P Steve Jobs

Steve Jobs: 1955-2011

Lord Ganesh

Lord Ganesha at the Reading Hindu Temple.

SQL Server Version and Build Number

I try to link various SQL Server versions (RTM, Service Packs, Cumulative Updates) with respective build numbers. Here are a few links that I use which might come in handy for many of you database developers out there. Some of these links are official and some aren’t.

1. How to determine the version and edition of SQL server and its components
2. SQL Server Version
3. SQL Server Version Database
4. sqlserverbuilds.blogspot.com

Some of the above links are comprehensive enough for most developers.

Some useful SQL Server functions to determine the version and edition:

@@VERSION
SERVERPROPERTY('productversion')
SERVERPROPERTY('productlevel')
SERVERPROPERTY('edition')

SQL Server identities: identity seed, increment and current value

I have been trying to get the current seed value for an identity column using SQL server object metadata. The only luck I have had so far is to get the seed value and the increment value that was used when a table with the identity was created. I thought the queries would come in handy at some time or the other in future. So, here it is.

SELECT  TABLE_SCHEMA,
	TABLE_NAME,
	IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_SEED_VALUE,
	IDENT_INCR(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_INCR_VALUE,
	IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) AS IDENT_CURRENT_VALUE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'TableHasIdentity') = 1
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'IsMSShipped') = 0
ORDER BY TABLE_SCHEMA, TABLE_NAME

Another variation of the above query also retrieves the name of the column with the identity specified.

select  sch.name as TABLE_SCHEMA,
	obj.name as TABLE_NAME,
	col.name as COLUMN_NAME,
	col.seed_value as IDENT_SEED_VALUE,
	col.increment_value as IDENT_INCR_VALUE,
	col.last_value as IDENT_CURRENT_VALUE
from sys.schemas as sch
join sys.objects as obj on obj.schema_id = sch.schema_id
join sys.identity_columns as col on col.object_id = obj.object_id
where obj.type = 'U' and obj.is_ms_shipped = 0
order by sch.name, obj.name

The SQL function IDENT_CURRENT() returns the current value or the last identity value generated for the table.

However, note that the SQL functions IDENT_SEED() and IDENT_INCR() only return the values that were used during the creation of the table. If you have reseeded the table identity since its creation, then the functions would still show the original values.

I would like to know if anyone has found a way to get the last seed value specified for any given table when it was reseeded after the table was created.

Post from iPhone

Just wanted to know how convenient it is to post from the iPhone. I seem to like it. The App serves the purpose.

I am yet discover the rest of the features of the app. So, that is all for now.

DateTime Manipulation: Continued

In my previous post DateTime Manipulation: Date Part or Time Part I had mentioned about how we can get the date part and time part from a given DateTime. A couple of days back I came across an article in sqlservercentral.com where another solution has been provided to get the date and time part out a given datetime using the DATEADD and DATEDIFF functions

Here is the link to the article Date Manipulation with DATEADD/DATEDIFF

Note: You may need to be logged into sqlservercentral.com to view this article

DateTime Manipulation: Date Part or Time Part

There are times when using SQL server 2000 or 2005 , you really want to use either the date-part or the time-part of a datetime value. But, you would still want to use it as a datetime value for comparison. Usually, as a solution, you would try and build a new datetime by converting the current datetime into a string or extracting the day, month, year etc. from the datetime using the respective SQL functions.

The SQL server’s default language settings may sometimes poses problems when building the new datetime using various parts of the datetime.

However, I found the following solution from here. It is a clean solution to convert the datetime to midnight or keep the time-part but change the date-part to 1900-01-01.

Here is the code:
SELECT GETDATE() AS [CURRENT_DATE],
       CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [DATE_PART],
       CAST(CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [TIME_PART]

Mumbai Assault Over?

Finally, after 60 hours of horror the assault on Mumbai is finally over. It is said that 3 more terrorists have been killed in the Taj and the NSG is running clear ups in the Taj.

It has also been said that they have a figure for the number of people dead in the Taj and seems to be much higher than expected. This mean the overall damage to human lives after the past 60 hours or so of the horror can be exceptionally high.

It is still not clear who these terrorists are. India has claimed that they have strong evidence about the involvement of Pakistan.

The Pakistan government now as earlier have made a U-turn regarding sending the ISI chief to India. I think the Indian government should what they can without the ISI support. I mean, why do we need the ISI? Aren’t we strong enough to make our own decisions?

I am glad that this horrific terror hours has finally come to an end. The aftermath of the attack is not likely to be that good also as the death toll keeps rising.

Mumbai assault still continues

I am watching NDTV very anxiously as I am typing this post. The firing and explosions continues even after 54 hours of gun battle. I am sure that the NSG commandos are doing their best to put an end to this last hurdle at the Taj Mahal Palace hotel. Even the highly trained troops of the Indian security service are finding it difficult to capture the one or two (according to NDTV) terrorists.

This activity like being put by the media and the security officials shows that the terrorists are highly trained and motivated to do anything to make a mark on India. When is it going to end? Well no one knows anything. Everyone can just wait and watch and the events unfold gradually in the course of the third night of battle.

It is sad that the NSG could not save any of the hostages in Nariman House. I feel sorry for the two year old Israely child who was orphaned on his second birthday. I feel sorry many other who were also not lucky.

So, is it time for the people to finally start thinking rationally about their role as citizens of this wonderful and strong nation? Does the common man really think that Enough is Enough? Will they continue to boast about the spirit of India, spirit of Mumbai and what not?

Currently the city and the nation is in shock, anger and distress not only about the scale of the attack but also about the failure of the government and intelligence to avoid this attack. Cleaning up mess is probably the only thing that the goverment and politicians can do.

I heard on the news that there was some politician who was talking about a bandh in Mumbai on the 1st of December. They don’t learn their lessons after all.

Last battle is still on at the Taj. Hope it will end soon.

India’s 9/11 on 26/11

It has been over 50 hours since the mumbai attacks began to unleash on the Taj Mahal Hotel, Oberoi Trident, Nariman House, CST Train Station and other places. It took all innocent people by surprise. Indians, foreign nationals, everyone. Even more surprising fact is that it came as a surprise to the Indian Government. There is a feeling of relief that this distressing act of terror is about to come to an end. The relief also comes with a feeling of sadness and sorrowfulness to know about the titanic nature of the loss of lives and injuries of the innocent people who had nothing to do with any of this.

I watched the news on Thursday evening on the BBCand ignored it for the evening thinking that it was one of many other attacks on the city of Mumbai in the past. But I really starting getting the grip of the intensity of this particular attack only the next morning. Since then I have tried to get as much information from the media over the internet and television about the latest happenings in the city. This is certainly the 9/11of India. 26/11 attacks is the biggest attack in the history of Mumbai in terms of the impact to the common man.

Once again, the politicians who lead our great “democratic” country have proved their incompetence in their position. It is again proved to me that these politicians are there to make a difference to them and not to the country. I must say I am not much into politics. But this event as it unleashes makes any common man of India make a judgement about the competence of the leaders of the country.

The un-inspiring robotic speech by our Prime Minister will not make any difference to the common man. The visits of the politicians to the hospitals and event sites are treated as a hindrance and nuisance.

There has been a serious failure of security. This should have never happened at all. There is no doubt that the Mumbai Police, the Marcos, the NSG has done a heroic job to save the innocent and obliterate the terrorists and they are doing the same as I am typing here. Not to forget the staff and other common people who played a heroic role to save lives. I salute the heroes. But the question again is how did these people sneak into the country through the Navy and coast guard vigil? Why it took those over 8 hours to reach the site. Why did the government take so much time to react to the situation.

I am sure I will have more reaction to this whole episode. But, the one thing that I would like to know is where Mr. Raj Thackeray is? Where are his MNS supporter who were ready to give life and take lives to get non amchisout of the state? Are they all sitting in a bunker and deciding which other citizens of the country that they can target next time? This is a time where I would have expected people like Mr. Raj Thakarey and his so called supporters to have come out in support of the needy. Where are you and your supporters Mr. Raj?

Over 150 people dead and over 320 people injured. This is not acceptable to any Indian. I am sure there is a change coming and hope that people will not take democracy for granted as they have been doing, some selected group going to the extent of giving it a new meaning, Gundaism.

Mumbai and India is in a terrible situation. Hope it all ends soon.

Dwarf Dance anyone?

Yesterday, we had a small get together to celebrate diwali, the festival of lights. There was a small cultural programme. And guess what, I had also taken part. Together with my wife and a mate and his wife, we had put put together a dwarf dance. And I could not resist putting it on YouTube. Here it is. It was a grand success yesterday and hope you like it.

New look blog

I got a little bored this evening and decided to change the theme for this blog. I have tried to have a simple theme this time. I like this theme it is simple and it is flexible width.

By they way a very happy Diwali to all readers.

Me is still there!!!!

It has been a while since I logged into my blog let alone blogging. It has been exactly a year since my last post. I don’t quite know the reason. May be I was bored, may be I did not have anything to blog about, I don’t know. But I am still here and I plan to make some effort to keep the blog alive.

Karaoke evening

Yesterday, we had a karaoke and dance evening. It was organised by the GSB community in the UK. 

I had a go at one of the many bollywood songs. Even Gautham had a go. It was all wonderful. We managed to sing in tune and without missing many beats.

It was an occasion for people from our community to come and meet and have fun.

I have posted some more pictures here.