Author |
Topic |
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-17 : 23:24:23
|
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
52326 Posts |
Posted - 2013-02-17 : 23:37:36
|
yep.. use INFORMATION_SCHEMA.COLUMNS catalog viewDECLARE @TableName varchar(100)SELECT @TableName = MIN(TABLE_NAME)FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE='BASE TABLE'WHILE @TableName IS NOT NULLBEGINIF 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.TABLESWHERE TABLE_TYPE='BASE TABLE'AND TABLE_NAME > @TableNameEND ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-17 : 23:57:57
|
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.TABLESWHERE TABLE_TYPE='BASE TABLE'WHILE @TableName IS NOT NULLBEGINIF 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.TABLESWHERE TABLE_TYPE='BASE TABLE'AND TABLE_NAME > @TableNameENDHere is the Error...Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'Test1'.Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'Test1'.Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'Test1'.Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'Test1'.Msg 102, Level 15, State 1, Line 1 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-18 : 00:02:40
|
May be single space is missing before ADD keyword...EXEC('ALTER TABLE ' + @TableName + ' Add Test1 Varchar(20)')--Chandu |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-18 : 00:08:45
|
Yes i was missing space, however same error.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 00:12:31
|
put a PRINT instead of EXEC and post the returned resultDECLARE @TableName varchar(100)SELECT @TableName = MIN(TABLE_NAME)FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE='BASE TABLE'WHILE @TableName IS NOT NULLBEGINIF 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.TABLESWHERE TABLE_TYPE='BASE TABLE'AND TABLE_NAME > @TableNameEND ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-18 : 00:15:53
|
[code]DECLARE @TableName varchar(100)SELECT @TableName = MIN(TABLE_NAME)FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE='BASE TABLE'WHILE @TableName IS NOT NULLBEGINIF 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.TABLESWHERE TABLE_TYPE='BASE TABLE'AND TABLE_NAME > @TableNameEND[/code]This is working fine for me...--Chandu |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-18 : 00:24:17
|
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... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 00:26:15
|
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 strangecan you post print result from adventurworks?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-18 : 00:34:17
|
Here it is....ALTER TABLE AdventureWorksDWBuildVersionAdd test2 Varchar(20) NULLALTER TABLE CUSTOMER_DATA_FILTEREDAdd test2 Varchar(20) NULLALTER TABLE CUSTOMER_FILTERED_DATAAdd test2 Varchar(20) NULLALTER TABLE DatabaseLogAdd test2 Varchar(20) NULLALTER TABLE DimAccountAdd test2 Varchar(20) NULLALTER TABLE DimCurrencyAdd test2 Varchar(20) NULLALTER TABLE DimCustomerAdd test2 Varchar(20) NULLALTER TABLE DimDepartmentGroupAdd test2 Varchar(20) NULLALTER TABLE DimEmployeeAdd test2 Varchar(20) NULLALTER TABLE DimGeographyAdd test2 Varchar(20) NULLALTER TABLE DimOrganizationAdd test2 Varchar(20) NULLALTER TABLE DimProductAdd test2 Varchar(20) NULLALTER TABLE DimProductCategoryAdd test2 Varchar(20) NULLALTER TABLE DimProductSubcategoryAdd test2 Varchar(20) NULLALTER TABLE DimPromotionAdd test2 Varchar(20) NULLALTER TABLE DimResellerAdd test2 Varchar(20) NULLALTER TABLE DimSalesReasonAdd test2 Varchar(20) NULLALTER TABLE DimSalesTerritoryAdd test2 Varchar(20) NULLALTER TABLE DimScenarioAdd test2 Varchar(20) NULLALTER TABLE DimTimeAdd test2 Varchar(20) NULLALTER TABLE EMPLOYEEAdd test2 Varchar(20) NULLALTER TABLE FactCurrencyRateAdd test2 Varchar(20) NULLALTER TABLE FactFinanceAdd test2 Varchar(20) NULLALTER TABLE FactInternetSalesAdd test2 Varchar(20) NULLALTER TABLE FactInternetSalesReasonAdd test2 Varchar(20) NULLALTER TABLE FactResellerSalesAdd test2 Varchar(20) NULLALTER TABLE FactSalesQuotaAdd test2 Varchar(20) NULLALTER TABLE FEMALE_DATAAdd test2 Varchar(20) NULLALTER TABLE FEMALE_TABLEAdd test2 Varchar(20) NULLALTER TABLE FEMALEDATAAdd test2 Varchar(20) NULLALTER TABLE FUZZY_EXACTAdd test2 Varchar(20) NULLALTER TABLE FUZZY_OUTPUT_TH_80Add test2 Varchar(20) NULLALTER TABLE FuzzyLookupMatchIndex_REF_TABLEAdd test2 Varchar(20) NULLALTER TABLE FuzzyLookupMatchIndex_REF_TABLE_FLRef_110805_22:17:16_6076_ec3365b3-6c00-40f2-a802-69373c6f1999Add test2 Varchar(20) NULLALTER TABLE FYZZY_OUTPUT_20Add test2 Varchar(20) NULLALTER TABLE MALE_TABLEAdd test2 Varchar(20) NULLALTER TABLE NON_MATCHING_FUZZYAdd test2 Varchar(20) NULLALTER TABLE OLE DB Des2tinationAdd test2 Varchar(20) NULLALTER TABLE OLE DB DestinationAdd test2 Varchar(20) NULLALTER TABLE OLE DB Destination 1Add test2 Varchar(20) NULLALTER TABLE OLE DB Destination234Add test2 Varchar(20) NULLALTER TABLE OLE DB Destination8Add test2 Varchar(20) NULLALTER TABLE PIVOT_OUTPUTAdd test2 Varchar(20) NULLALTER TABLE ProspectiveBuyerAdd test2 Varchar(20) NULLALTER TABLE REF_TABLEAdd test2 Varchar(20) NULLALTER TABLE ResumesAdd test2 Varchar(20) NULLALTER TABLE Resumes_StageAdd test2 Varchar(20) NULLALTER TABLE testAdd test2 Varchar(20) NULLALTER TABLE tg_DataCleaningMaintenance_PendingDelete__20110805_000000_0d809d68-ce11-4a14-8f00-aabdfac13329Add test2 Varchar(20) NULLALTER TABLE tg_DataCleaningMaintenance_PendingInsert__20110805_000000_b000b27c-5bf7-4f68-b147-a71fc804a500Add test2 Varchar(20) NULL |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-18 : 00:40:50
|
see there is no space between AdventureWorksDWBuildVersion and Add ( AdventureWorksDWBuildVersionAdd) ..............?--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 00:52:22
|
its the space issue as Bandi pointed out. use it exactly as suggested by putting a space before Add------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-18 : 12:57:30
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 13:03:49
|
[code]DECLARE @TableName varchar(100)SELECT @TableName = MIN(TABLE_NAME)FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE='BASE TABLE'WHILE @TableName IS NOT NULLBEGINIF 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)>2EXEC('ALTER TABLE ' + @TableName + ' Add name Varchar(20)')SELECT @TableName = MIN(TABLE_NAME)FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE='BASE TABLE'AND TABLE_NAME > @TableNameEND[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-18 : 14:09:09
|
Awesome. Thank You... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-19 : 00:06:04
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|