Asked  7 Months ago    Answers:  5   Viewed   74 times

I need to add a specific column if it does not exist. I have something like the following, but it always returns false:

IF EXISTS(SELECT *
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'myTableName'
                 AND COLUMN_NAME = 'myColumnName') 

How can I check if a column exists in a table of the SQL Server database?

 Answers

52

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END
Tuesday, June 1, 2021
 
pwaring
answered 7 Months ago
59

It depends on what you want to test exactly.

Information schema?

To find "whether the table exists" (no matter who's asking), querying the information schema (information_schema.tables) is incorrect, strictly speaking, because (per documentation):

Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

The query provided by @kong can return FALSE, but the table can still exist. It answers the question:

How to check whether a table (or view) exists, and the current user has access to it?

SELECT EXISTS (
   SELECT FROM information_schema.tables 
   WHERE  table_schema = 'schema_name'
   AND    table_name   = 'table_name'
   );

The information schema is mainly useful to stay portable across major versions and across different RDBMS. But the implementation is slow, because Postgres has to use sophisticated views to comply to the standard (information_schema.tables is a rather simple example). And some information (like OIDs) gets lost in translation from the system catalogs - which actually carry all information.

System catalogs

Your question was:

How to check whether a table exists?

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   AND    c.relkind = 'r'    -- only tables
   );

Use the system catalogs pg_class and pg_namespace directly, which is also considerably faster. However, per documentation on pg_class:

The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also pg_index), sequences, views, materialized views, composite types, and TOAST tables;

For this particular question you can also use the system view pg_tables. A bit simpler and more portable across major Postgres versions (which is hardly of concern for this basic query):

SELECT EXISTS (
   SELECT FROM pg_tables
   WHERE  schemaname = 'schema_name'
   AND    tablename  = 'table_name'
   );

Identifiers have to be unique among all objects mentioned above. If you want to ask:

How to check whether a name for a table or similar object in a given schema is taken?

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   );
  • Related answer on dba.SE discussing "Information schema vs. system catalogs"

Alternative: cast to regclass

SELECT 'schema_name.table_name'::regclass

This raises an exception if the (optionally schema-qualified) table (or other object occupying that name) does not exist.

If you do not schema-qualify the table name, a cast to regclass defaults to the search_path and returns the OID for the first table found - or an exception if the table is in none of the listed schemas. Note that the system schemas pg_catalog and pg_temp (the schema for temporary objects of the current session) are automatically part of the search_path.

You can use that and catch a possible exception in a function. Example:

  • Check if sequence exists in Postgres (plpgsql)

A query like above avoids possible exceptions and is therefore slightly faster.

to_regclass(rel_name) in Postgres 9.4+

Much simpler now:

SELECT to_regclass('schema_name.table_name');

Same as the cast, but it returns ...

... null rather than throwing an error if the name is not found

Tuesday, June 1, 2021
 
muaaz
answered 7 Months ago
60

Use XQuery:

declare @xml xml =
'<email>
  <account language="en" />
</email>'

select @xml.value('(/email/account/@language)[1]', 'nvarchar(max)')

declare @t table (m xml)

insert @t values 
    ('<email><account language="en" /></email>'), 
    ('<email><account language="fr" /></email>')

select m.value('(/email/account/@language)[1]', 'nvarchar(max)')
from @t

Output:

en
fr
Friday, July 30, 2021
 
Bruno Reis
answered 4 Months ago
31

You need to change the DateTime types in the signature of your Function Method. SQLDateTime maps to a DateTime on the database.

System.DateTime is more precise and can be mapped to DateTime2 (but by default, it'll be dropped as a DateTime in the deploy script).

[SqlFunction(DataAccess = DataAccessKind.None)]
//OLD Signature public static SqlDateTime UTCToLocalDT(SqlDateTime val) 
public static DateTime UTCToLocalDT(DateTime val) {
   ...
}

Then you can tweak your deploy script to read.

CREATE FUNCTION [UTCToLocalDT]
(
    @dt [datetime2]
)
RETURNS [datetime2]
AS
    EXTERNAL NAME [SQLCLR].[MyCompany.SQLCLR.DateTimeHelpCLR].UTCToLocalDT
GO

Running your function should now give you more precise output.

DECLARE @input DateTime2, @output DateTime2
SET @input = '2010-04-12 09:53:44.48123456'
SET @output = YourDatabase.dbo.[UTCToLocalDT](@input)
SELECT @input, @output
Saturday, August 14, 2021
 
Stubbi
answered 4 Months ago
19

Thanks to everyone who offered a solution, gathering up some of the answers, I came up with my own version of solution. Maybe it's not the best solution around, but at least I don't need an extra dll to add to the references or deal with some stored procedures Access won't support.

OleDbConnection con = new OleDbConnection("my database address");
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 1 date FROM myTable";
con.Open();
bool exists = true;
try
{
  var x = cmd.ExecuteScalar();
}
catch (Exception e)
{
  exists = false;
}
con.Close();
Thursday, August 26, 2021
 
sophie
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share