Dear All,
I wanted to know how do I know or check that whether a column exists in a table. What function or method should I use to find out that a column has already exists in a table.
When I run a T-SQL script which i have written does not work. Here is how I have written:
IF Object_ID('ColumnA') IS NOT NULL
ALTER TABLE [dbo].[Table1] DROP COLUMN [ColumnA]
GO
I badly need some help.
Apologies if this has been answered before, but the "Search" function doesn't seem to be working on these forums the last couple of days.
I'd just like to check if a table already exists before dropping it so that I can avoid an error if it doesn't exist. Doing a web search, I've tried along the lines of
"If (object_id(sensor_stream) is not null) drop table sensor_stream"
and
"If exists (select * from sensor_stream) drop table sensor_stream"
In both of these cases I get the error: "There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = if ]"
Sooooo... what is the standard way to check for existence of a table before dropping it? Someone help? This seems like it should be simple, but I can't figure it out.
Thanks in advance!
Dana
Check the field existence of a database table, if exist get the type, size, decimal ..etc attributes
I need SP
SP
(
@Tablename varchar(30),
@Fieldname varchar(30),
@existance char(1) OUTPUT,
@field_type varchar(30) OUTPUT,
@field_size int OUTPUT,
@field_decimal int OUTPUT
)
as
/* Below check the existance of a @Fieldname in given @Tablename */
/* And set the OUTPUT variables */
Thanks
This function, F_TEMP_TABLE_EXISTS, checks for the existence of a temp table (## name or # name), and returns a 1 if it exists, and returns a 0 if it doesn't exist.
The script creates the function and tests it. The expected test results are also included.
This was tested with SQL 2000 only.
if objectproperty(object_id('dbo.F_TEMP_TABLE_EXISTS'),'IsScalarFunction') = 1
begin drop function dbo.F_TEMP_TABLE_EXISTS end
go
create function dbo.F_TEMP_TABLE_EXISTS
( @temp_table_name sysname )
returns int
as
/*
Function: F_TEMP_TABLE_EXISTS
Checks for the existence of a temp table
(## name or # name), and returns a 1 if
it exists, and returns a 0 if it doesn't exist.
*/
begin
if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U')and
o.id = object_id( N'tempdb..'+@temp_table_name )
)
begin return 1 end
return 0
end
go
print 'Create temp tables for testing'
create table #temp (x int)
go
create table ##temp2 (x int)
go
print 'Test if temp tables exist'
select
[Table Exists] = dbo.F_TEMP_TABLE_EXISTS ( NM ),
[Table Name] = NM
from
(
select nm = '#temp' union all
select nm = '##temp2' union all
select nm = '##temp' union all
select nm = '#temp2'
) a
print 'Check if table #temp exists'
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
print '#temp exists'
else
print '#temp does not exist'
print 'Check if table ##temp4 exists'
if dbo.F_TEMP_TABLE_EXISTS ( '##temp4' ) = 1
print '##temp4 exists'
else
print '##temp4 does not exist'
go
-- Drop temp tables used for testing,
-- after using function F_TEMP_TABLE_EXISTS
-- to check if they exist.
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
begin
print 'drop table #temp'
drop table #temp
end
if dbo.F_TEMP_TABLE_EXISTS ( '##temp2' ) = 1
begin
print 'drop table ##temp2'
drop table ##temp2
end
Test Results:
Create temp tables for testing
Test if temp tables exist
Table Exists Table Name
------------ ----------
1 #temp
1 ##temp2
0 ##temp
0 #temp2
(4 row(s) affected)
Check if table #temp exists
#temp exists
Check if table ##temp4 exists
##temp4 does not exist
drop table #temp
drop table ##temp2
CODO ERGO SUM
I have a webpage where users can connect with other users by sending them a request. Sending the request just puts a row in a connect table that contains the users id, the targetusers id, a requesteddate, an accepted date and a disconnectdate (for the original requester to end the connection and a reject bit the the target can reject the request. Hoever I need to check to assure that the connect does nt already exist either as pending (requestdate is not null and accept date is null) or both dates are not null (connection already complete.).
ALTER PROCEDURE [dbo].[requestConnect]
-- Add the parameters for the stored procedure here
@requestor int,
@requested int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
[Code] ....
we tried out the following code in query analyser -
create procedure TrialProc
as
select * from sakjdhf
when we executed this piece of TSQL in query analyser, we expected it to give an error or warning that no object by the name of sakjdhf exists ( as actually there is no such table or view in the database ). however to our surprise we got "command completed successfully " !!
does this mean the SQL server does not check for necessary objects when creating a stored procedure ? or is there some setting that we missed out whihch is causing SQL server to overlook the error in the code ?
I'm trying to figure out the best way to write a script to deploy environment variables to different servers. To create the variable I'm currently using catalog. create_environment_variable. I want to wrap that in an if not exist statement.I had thought about just blowing away all the variables and recreating them but I thought that wouldn't go over well in prod. I wasn't sure if by deleting the variable, references to the variable would be lost.
View 3 Replies View RelatedI have a data flow task inside a foreach loop container which will take multiple excel files and load into a sql table. Once all the excel files are loaded into the table, then at the end one of the column gets updated using execute sql task. Now I am trying to check for a file existence, if the file is not present in the folder then the data flow task should not be executed. Any help is greatly appreciated, I am thinking of using file system task for this, but not exactly sure. Thanks in advance.
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
I have created a table and want to alter that table.
I want to add identity to my existence column.
How to add identity to my existence column?
Hi All,
We have a very convoluted ETL system which is pulling unnecessary data. First thought is to restrict everything so that only the columns/tables that are necessary are brought back. We have a tons of reporting stored procedures that depend on ETL tables, is there anyway we can find out which column/tables these stored procedures are using?
Thanks!
I have a table column can save english(single byte) or chinese(double byte) char, how to distinguish the records containing chinese(double byte) char thru sql command
thx.
I am trying
IF OBJECT_ID(''tempdb..#tempTable.Column'') IS NOT NULL
But its returning me a null every time. Is there any other way to check if column exists in temporary table.
Hello all,
I am trying to add columns to several tables in a singular database. I want to check to see if the column exists in a given table first before executing the alter table command so that I can skip that command if the column already exists, and avoid aborting my script prematurely. I can't seem to find a way to check for the column's existance. Any suggestions?
As to not get lost in the details of my problem, I'm oversimplifying this posting to get the root of my problem answered. :-)
Let's say a database has a table called CUSTOMERS, and it may or may not contain a column called ORDER_NUMBER, depending on whether or not the database is an old or new database.
Now if I run the following query in Query Analyzer (v8)...
if (1=2) begin
select * from CUSTOMERS where ORDER_NUMBER is not null
end
...I get "Invalid column name 'ORDER_NUMBER'."
Obviously 1 does not equal 2, so why is the code in that if block being executed? Or is there some sort of precompilation/schema checking that is going on which is causing the error? If so, can I set something to not have the code be precompiled?
Table ATable B
AIDBID
Col 1BCol1
Col 2
Col3
I want to put a check constraint on A.col3 that depends on the values of A.Col1, A.Col2 and B.BCol1
How can I achieve this without using function.
Hi,
I have a table with the follwing values
Table Cats
{
CatID, date
-----------------------
Cat1, D1
Cat2, D2
Cat3, D3
}
I just wanted to check whether Cat1 exists in the table. Can anyone post the query
Thanks
~MOhan
Hi!
SS2005:
create table #tmp(a int)
create index idxt1 on #tmp(a)
insert into #tmp values (42)
select * from sys.indexes
where name like '%idx%'
order by name
But I can't see any rows about idxt1 :-(
If I say
create index idxt1 on #tmp(a)
againg I got error, because it exists.
How to check for existence using query?
B. D. Jensen
I'm familiar with how to check for the existence of a table before dropping it using the following command:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xxx]
How does one check for the existence of a temp table (using # syntax) before dropping it? I've tried various flavors of this command and none work. One flavor is
use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#xxx]
CREATE TABLE #xxx (
NumID INTEGER IDENTITY(1,1),
Exhibitor_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL
)
Thanks.
Nick
Hi all,
While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database: SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1of course I removed it from the IF just for testing purposes, but my quandry is this...
Why chose that select (converting table name to object ID) rather than just doing THIS:SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1
I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).
A follow-on question is this:
When I try to do the same check from another server (i.e.SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1) it of course fails because OBJECTPROPERTY only looks for the id on the local database.
So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?
Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that ;) )
I am using the following script to check existence of table in the Database and create it dynamically...
This is working when table not existed, it error-ed when the table existed...
This script i am using in the Exec Sql Task.....
[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50)
declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not
set correctly, or connection not established correctly.
declare @ODSDB varchar(50)
declare @SQLSTMT varchar(max)
set @ODSDB = 'SampleDB'
begin
set @SQLSTMT = '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')
[Code] ...........
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies View RelatedI have two columns.
policyNumber contains a 12-13 varchar string
AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) seperated by commas
I want to check if policyNumber is contained in AllPolicyNumbersIncluded?
I have policyNumber LIKE AllPolicyNumbersIncluded which works when only one policy number is in AllPolicyNumbersIncluded and incidently works switched around AllPolicyNumbersIncluded LIKE policyNumber I assume because they are equal.
Can anyone tell me how to check if one column's value is a substring of another - without going through every possible substring of the second
How can I test to see if a column exists before adding a column to a sql mobile table?
thanks,
Luis
I want to fin out the number of records that are not numeric on my database, how can I fin that using an SQL query?
View 2 Replies View RelatedHello All,
Can someone pls help me with how to check for a numeric value in a varchar column?
For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?
Thanks in advance!!
Can't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.
I tried using isnumeric, but it also allows those with periods, comma's etc.
Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.
As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.
I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...
In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits
e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
SELECT af.AccomType, af.AccomRef, af.AccomName,af.address1, af.address2, bf1.RoomCode,
Convert(Int,Replace(Replace(Replace(Replace(REPLAC E(Replace(MAX(bf1.EndBook),'°','200'),'´','204'),' 99','1999'),'97','1997'),'47',1947),'98','1998')) AS max_date,
...............
WHERE
af.Resort=@strResort
AND
(af.AccomType = 'H' OR af.AccomType = 'O')
AND
max_date>20040721
order by max_date.
Problem is I get an error saying invalid column max_date. It works in the order by clause when I get rid of the
'max_date>20040721 '.
Thanks
I am trying to query multiple columns for a specific value. I have 8 columns (values are either 1 or 0)and I want to query the table to find out which rows contain zero's in ALL of the 8 columns. Whats the best way to do this? I can create a lenghty select statement where column1 =0 and column2 =0 and column3=0 and column4 =0 and column5 =0 .... etc. I was wondering if there was an easier way to do this?
Thanks in Advance
Shankar.N
I have 5 to six id columns from my excel input.they can be empty in some cases but of the five columns atleaset one column should have some value....All the five columns should not be empty.
How do i check this scenario...
eg:Input column
ID1 ID2 ID3 ID4 ID5
12 NULL NULL 67 78
I should check to see atleast one column should have some value
How can i achieve this???
Please let me know
Can you tell me if this is possible? (and how to do it!!)
The application is VS2005, with sql database.
I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.
I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.
Well, actually, as in the title. I have a table. The script should add a column if that column doesn't exist already. I use VB to combine the two queries. So what I want:
Query1: Does the column exists:
if No,
query2: create the column
How can I achieve this?
I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???
Thanks in advance!
Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'end_date' references another column, table 'Session'.
Here's an example of the script that I'm using:
CREATE TABLE Session (
session_key char(18) NOT NULL,
course_key char(18) NOT NULL,
site_key char(18) NOT NULL,
instructor_key char(18) NOT NULL,
start_date smalldatetime NULL,
end_date smalldatetime NULL
CHECK (end_date >= start_date)
)
.