SQL Server 2005 Query

EOY- End Of Year.. Happy New Year Everyone !!!

Well I had to update a Column End_Date to a particular value '12/31/2010' since it is the EOY. Instead of viewing each table and editing all the columns, I thought of writing a SQL Query to update all the End_Date to '12/31/2010' in all the tables.

SysObjects - It has the list of all tables present in the database.
SysColumns Table- It has the list of all the columns in all the tables in the database.
SysObjects and SysColumns are linked by "id" .

So here is the query to display all the table names based on the column end_dt.

SELECT SYSOBJECTS.NAME FROM SYSCOLUMNS
INNER JOIN SYSOBJECTS ON
SYSCOLUMNS.ID = SYSOBJECTS.ID
WHERE SYSCOLUMNS.NAME='END_DT';

Then I had to loop through each table using cursors and use the update statement to change the end_dt values.

Full Query :

SET QUOTED_IDENTIFIER ON
DECLARE @TableName varchar(100)
DECLARE @SQL VARCHAR(100)
DECLARE @getTableName CURSOR
SET @getTableName= CURSOR FOR
SELECT SYSOBJECTS.NAME FROM SYSCOLUMNS
INNER JOIN SYSOBJECTS ON
SYSCOLUMNS.ID = SYSOBJECTS.ID
WHERE SYSCOLUMNS.NAME='END_DT';
OPEN @getTableName
FETCH NEXT
FROM @getTableName INTO @TableName
while @@fetch_status = 0
begin
SET @SQL='update ' + @TableName + ' set end_dt=''12/31/2099'' where end_dt is null'
EXECUTE(@SQL)
fetch next
from @getTableName into @TableName
END
close @getTableName
deallocate @getTableName

Comments

Post a Comment

Popular posts from this blog

Knowing too much is bad(in the world of OOP)

In real life, we have heard experts say that knowing too much or sometimes doing too much is bad.  This is the same in the world of Object Oriented Programming, this is called as God Object. The God object is an example of anti-pattern(code that is difficult to understand, refactor and maintain) and a code smell.  We have heard of Single-responsibility Principle(which is "S" in the SOLID principles). The SRP states that each class should have only responsibility and therefore has only one reason to change.  Whenever a code is written, make sure that the class does only one thing. You can refer to the God Object here at wikipedia which gives a brief description: https://en.wikipedia.org/wiki/God_object
Read more

Reverse a LinkedList Algorithm

Reversing a Linked List Problem Statement:  Reverse a Linked List.  Given a Linked List of Nodes  :  1->2->3->4->5->6 , reverse it so that the output is 6->5->4->3->2->1 My Approach:  For this approach, we cannot the same LinkedListNode since it would point to the same memory location. In the above example, 1 should point to null of the new List. 2 which is 1.next (original) should point to 1 of the new List. 3 which is 2.next (original) should point to 2 of the new List. 4 which is 3.next (original) should point to 3 of the new List. 5 which is 4.next (original) should point to 4 of the new List. 6 which is 5.next (original) should point to 5 of the new List. This is where our logic ends. 1) LinkedListNode Class  This class has 2 Components of a Singly Linked List.  Value of the node Pointer to the Next Node.           public class LinkedListNode ...
Read more

DateTime.Now in C#

DateTime Class is used to get the various possibilities that can be included within a day, month or a year. I am going to discuss about some functionalities of DateTime.Now here. For example: To get the current date and time, use DateTime.Now which gives us 6/24/2009 9:45:00 AM DateTime.Now has 5 important functions to get date and time in different formats. 1) DateTime.Now.ToLongDateString() - Displays in the format "Wednesday, June 24, 2009." 2) DateTime.Now.ToLongTimeString() - Displays in the format "12:00:00 AM. " 3) DateTime.Now.ToShortDateString() - Displays in the format "6/24/2009" 4) DateTime.Now.ToShortTimeString() - Displays in the format "12:00 AM" 5) DateTime.Now.ToString("Format") - This method accepts different formats. The formats can be obtained from GetDateTimeFormats() method of DateTime.Now and has 133 different formats that anyone can think of while displaying Date and Time. For example: DateTime.Now.ToString(...
Read more