| Author |
 Topic  |
|
|
Sonu619
Posting Yak Master
195 Posts |
 Posted - 02/17/2013 : 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
India
47173 Posts |
Posted - 02/17/2013 : 23:37:36
|
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/
|
 |
|
|
Sonu619
Posting Yak Master
195 Posts |
Posted - 02/17/2013 : 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.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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 02/18/2013 : 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
195 Posts |
Posted - 02/18/2013 : 00:08:45
|
| Yes i was missing space, however same error.... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/18/2013 : 00:12:31
|
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/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 02/18/2013 : 00:15:53
|
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 |
|
|
|
Sonu619
Posting Yak Master
195 Posts |
Posted - 02/18/2013 : 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
India
47173 Posts |
Posted - 02/18/2013 : 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 strange
can you post print result from adventurworks?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Sonu619
Posting Yak Master
195 Posts |
Posted - 02/18/2013 : 00:34:17
|
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
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 02/18/2013 : 00:40:50
|
see there is no space between AdventureWorksDWBuildVersion and Add ( AdventureWorksDWBuildVersionAdd) ..............?
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/18/2013 : 00:52:22
|
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/
|
|
|
|
Sonu619
Posting Yak Master
195 Posts |
Posted - 02/18/2013 : 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
India
47173 Posts |
Posted - 02/18/2013 : 13:03:49
|
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/
|
|
|
|
Sonu619
Posting Yak Master
195 Posts |
Posted - 02/18/2013 : 14:09:09
|
| Awesome. Thank You... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/19/2013 : 00:06:04
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Query Help....
