check that a column exists or not on a table
-
hello!
i have to write a script which can check that a specified table (call TBL1) has the specified column (call COL1) or not 'couse TBL1 has no COL1, i have to add that to the table. (i use SQL S 2K.)
can u help me?
thx,
G
-
The following should return a list of columns on table TBL1:
select c.name from syscolumns c inner join sysobjects o
on c.id = o.id where o.name = 'TBL1'
-
wow! thats cool!
thank u Jesper!
G
-
-
No need to query the system tables directly. It's better to use something like
USE Northwind
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders'
or even
EXEC sp_columns 'Orders'
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
hi!
my solution is the next:
IF 'COL1' NOT IN
(SELECT c.Name
FROM SYSCOLUMNS c INNER JOIN SYSOBJECTS o ON c.ID = o.ID
WHERE o.Name = 'TBL1')
BEGIN
ALTER TABLE TBL1 ADD COL1 int IDENTITY NOT NULL
END
ELSE
BEGIN
PRINT 'TBL1 has COL1'
END
-
I use this code to add a standard set of output columns (if they don't already exist) to tables for use by our address cleaning software. This code requires the parameters @dbName and @tblIn, and is written to only handle tables owned by dbo.
SET
QUOTED_IDENTIFIER OFF
EXEC ('use [' + @dbName + "]
DECLARE @sql varchar(1000)
SELECT @sql = ISNULL(@sql + ', ', '') + column_name + ' ' + column_desc
FROM (
SELECT 'CleanFullName' AS column_name, 'varchar(50)' as column_desc
UNION ALL SELECT 'CleanFirstName' AS column_name, 'varchar(50)' as column_desc
UNION ALL SELECT 'CleanLastName' AS column_name, 'varchar(50)' as column_desc
UNION ALL SELECT 'CleanAddress1' AS column_name, 'varchar(50)' as column_desc
UNION ALL SELECT 'CleanAddress2' AS column_name, 'varchar(50)' as column_desc
UNION ALL SELECT 'CleanAddress3' AS column_name, 'varchar(50)' as column_desc
UNION ALL SELECT 'CleanCity' AS column_name, 'varchar(35)' as column_desc
UNION ALL SELECT 'CleanState' AS column_name, 'char(2)' as column_desc
UNION ALL SELECT 'CleanZip5' AS column_name, 'char(5)' as column_desc
UNION ALL SELECT 'CleanZip4' AS column_name, 'char(4)' as column_desc
UNION ALL SELECT 'AddressStatus' AS column_name, 'smallint' as column_desc
UNION ALL SELECT 'SuppressionFlag' AS column_name, 'bit not null DEFAULT (0)' as column_desc
) c
WHERE column_name NOT IN (
SELECT column_name from INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '" + @tblIN + "'
)
IF LEN(@sql) > 0
EXEC('ALTER TABLE [" + @tblIN + "] ADD ' + @sql)
")
-
Any help would be appreciated in this silly little problem.
I need to insert a column into a table, that I get from a linked server,(standard alter table and ADD ), but nowhere can I find (BOL or MSDN) how to force the ordinal position of the column.
There seem to be plenty of ways to discover the ordinal position but no way to specify it.
Only solution I can see is moving the data to a temp table and then drop and recreate the table. There has to be a better way that I'm missing.
Many thanks
-
New columns are always last, as far as I know.
Instead of moving data to a temp table, you might want to simply rename the original table. Then you only have to move data once.
-
To check for existence of a column, don't think you need to do a system table query at all, just use a built-in SQL function:IF COL_LENGTH ('tableName', 'columnName') IS NULL--column does not existSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply