Query Help.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help....

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 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

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.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
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 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
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.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[/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 strange

can you post print result from adventurworks?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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) 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
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 MVP
http://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.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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources