SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts

Posted - 02/17/2013 :  23:24:23  Show Profile  Reply with Quote
Hi guys,

I am not sure it is possible or not but would like to ask here.

I want know is anyone know any TRICK/Query that search through all tables in the database and IF column is there that's fine otherwise create a new column.

Please let me know if it is make sense?

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/17/2013 :  23:37:36  Show Profile  Reply with Quote
yep.. use INFORMATION_SCHEMA.COLUMNS catalog view


DECLARE @TableName varchar(100)

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'


WHILE @TableName IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME = 'your column name')
EXEC('ALTER TABLE ' + @TableName + ' ADD ColumnNameHere datatype NULL/NOT NULL')

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME > @TableName
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/17/2013 :  23:57:57  Show Profile  Reply with Quote
Visakh Thank you for your reply. I am using Below Statement and i am having a Error. Please guide me where i am wrong.


DECLARE @TableName varchar(100)

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'


WHILE @TableName IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME = 'Test1')
EXEC('ALTER TABLE ' + @TableName + 'Add Test1 Varchar(20)')

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME > @TableName
END

Here is the Error...
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Test1'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Test1'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Test1'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Test1'.
Msg 102, Level 15, State 1, Line 1
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/18/2013 :  00:02:40  Show Profile  Reply with Quote
May be single space is missing before ADD keyword...
EXEC('ALTER TABLE ' + @TableName + ' Add Test1 Varchar(20)')



--
Chandu
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/18/2013 :  00:08:45  Show Profile  Reply with Quote
Yes i was missing space, however same error....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  00:12:31  Show Profile  Reply with Quote
put a PRINT instead of EXEC and post the returned result

DECLARE @TableName varchar(100)

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'


WHILE @TableName IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME = 'Test1')
PRINT('ALTER TABLE ' + @TableName + 'Add Test1 Varchar(20) NULL')

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME > @TableName
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/18/2013 :  00:15:53  Show Profile  Reply with Quote

DECLARE @TableName varchar(100)

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

WHILE @TableName IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME = 'name')
EXEC('ALTER TABLE ' + @TableName + ' Add  name Varchar(20)')

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME > @TableName
END

This is working fine for me...

--
Chandu
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/18/2013 :  00:24:17  Show Profile  Reply with Quote
If I execute above statement on Personal DB (Its working fine)
But if i execute same statement on Adventure Works DB giving me error. Any way works fine with me Print Statement.

Thank You Guys...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  00:26:15  Show Profile  Reply with Quote
quote:
Originally posted by Sonu619

If I execute above statement on Personal DB (Its working fine)
But if i execute same statement on Adventure Works DB giving me error. Any way works fine with me Print Statement.

Thank You Guys...


thats a bit strange

can you post print result from adventurworks?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/18/2013 :  00:34:17  Show Profile  Reply with Quote
Here it is....
ALTER TABLE AdventureWorksDWBuildVersionAdd test2 Varchar(20) NULL
ALTER TABLE CUSTOMER_DATA_FILTEREDAdd test2 Varchar(20) NULL
ALTER TABLE CUSTOMER_FILTERED_DATAAdd test2 Varchar(20) NULL
ALTER TABLE DatabaseLogAdd test2 Varchar(20) NULL
ALTER TABLE DimAccountAdd test2 Varchar(20) NULL
ALTER TABLE DimCurrencyAdd test2 Varchar(20) NULL
ALTER TABLE DimCustomerAdd test2 Varchar(20) NULL
ALTER TABLE DimDepartmentGroupAdd test2 Varchar(20) NULL
ALTER TABLE DimEmployeeAdd test2 Varchar(20) NULL
ALTER TABLE DimGeographyAdd test2 Varchar(20) NULL
ALTER TABLE DimOrganizationAdd test2 Varchar(20) NULL
ALTER TABLE DimProductAdd test2 Varchar(20) NULL
ALTER TABLE DimProductCategoryAdd test2 Varchar(20) NULL
ALTER TABLE DimProductSubcategoryAdd test2 Varchar(20) NULL
ALTER TABLE DimPromotionAdd test2 Varchar(20) NULL
ALTER TABLE DimResellerAdd test2 Varchar(20) NULL
ALTER TABLE DimSalesReasonAdd test2 Varchar(20) NULL
ALTER TABLE DimSalesTerritoryAdd test2 Varchar(20) NULL
ALTER TABLE DimScenarioAdd test2 Varchar(20) NULL
ALTER TABLE DimTimeAdd test2 Varchar(20) NULL
ALTER TABLE EMPLOYEEAdd test2 Varchar(20) NULL
ALTER TABLE FactCurrencyRateAdd test2 Varchar(20) NULL
ALTER TABLE FactFinanceAdd test2 Varchar(20) NULL
ALTER TABLE FactInternetSalesAdd test2 Varchar(20) NULL
ALTER TABLE FactInternetSalesReasonAdd test2 Varchar(20) NULL
ALTER TABLE FactResellerSalesAdd test2 Varchar(20) NULL
ALTER TABLE FactSalesQuotaAdd test2 Varchar(20) NULL
ALTER TABLE FEMALE_DATAAdd test2 Varchar(20) NULL
ALTER TABLE FEMALE_TABLEAdd test2 Varchar(20) NULL
ALTER TABLE FEMALEDATAAdd test2 Varchar(20) NULL
ALTER TABLE FUZZY_EXACTAdd test2 Varchar(20) NULL
ALTER TABLE FUZZY_OUTPUT_TH_80Add test2 Varchar(20) NULL
ALTER TABLE FuzzyLookupMatchIndex_REF_TABLEAdd test2 Varchar(20) NULL
ALTER TABLE FuzzyLookupMatchIndex_REF_TABLE_FLRef_110805_22:17:16_6076_ec3365b3-6c00-40f2-a802-69373c6f1999Add test2 Varchar(20) NULL
ALTER TABLE FYZZY_OUTPUT_20Add test2 Varchar(20) NULL
ALTER TABLE MALE_TABLEAdd test2 Varchar(20) NULL
ALTER TABLE NON_MATCHING_FUZZYAdd test2 Varchar(20) NULL
ALTER TABLE OLE DB Des2tinationAdd test2 Varchar(20) NULL
ALTER TABLE OLE DB DestinationAdd test2 Varchar(20) NULL
ALTER TABLE OLE DB Destination 1Add test2 Varchar(20) NULL
ALTER TABLE OLE DB Destination234Add test2 Varchar(20) NULL
ALTER TABLE OLE DB Destination8Add test2 Varchar(20) NULL
ALTER TABLE PIVOT_OUTPUTAdd test2 Varchar(20) NULL
ALTER TABLE ProspectiveBuyerAdd test2 Varchar(20) NULL
ALTER TABLE REF_TABLEAdd test2 Varchar(20) NULL
ALTER TABLE ResumesAdd test2 Varchar(20) NULL
ALTER TABLE Resumes_StageAdd test2 Varchar(20) NULL
ALTER TABLE testAdd test2 Varchar(20) NULL
ALTER TABLE tg_DataCleaningMaintenance_PendingDelete__20110805_000000_0d809d68-ce11-4a14-8f00-aabdfac13329Add test2 Varchar(20) NULL
ALTER TABLE tg_DataCleaningMaintenance_PendingInsert__20110805_000000_b000b27c-5bf7-4f68-b147-a71fc804a500Add test2 Varchar(20) NULL
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/18/2013 :  00:40:50  Show Profile  Reply with Quote
see there is no space between AdventureWorksDWBuildVersion and Add ( AdventureWorksDWBuildVersionAdd) ..............?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  00:52:22  Show Profile  Reply with Quote
its the space issue as Bandi pointed out. use it exactly as suggested by putting a space before Add

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/18/2013 :  12:57:30  Show Profile  Reply with Quote
Yep My Bad...
If i want to add one condition. "If existing table has more then two columns then add new column" How i can add this condition?

Thank You.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  13:03:49  Show Profile  Reply with Quote

DECLARE @TableName varchar(100)

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

WHILE @TableName IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME = 'name')
AND (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName)>2
EXEC('ALTER TABLE ' + @TableName + ' Add  name Varchar(20)')

SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME > @TableName
END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/18/2013 :  14:09:09  Show Profile  Reply with Quote
Awesome. Thank You...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/19/2013 :  00:06:04  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000