DROP TABLE If It Exists

Feb 9, 2008

 i am using vb.net and ms sql server 2005 express.....
what is the syntax for dropping a table if exists
i have used this but it says incorrect syntax near if 
Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb))
 cmda.Connection.Open()
        cmda.ExecuteNonQuery()
       cmda.Connection.Close()
any solutions???? plz only answer in vb.net and sql server express

View 8 Replies


ADVERTISEMENT

Drop A Table If I Exists

Feb 18, 2004

in mysql i can drop a table or db if it currently exists using

drop table if exists [table1] or
drop database if exists [db1]

is there an equalivant in ms sql


thanks

View 4 Replies View Related

DROP TABLE IF EXISTS (not Working)!!

Mar 31, 2004

I need to drop a table if exist ?? how can i do that ??

thanx !

View 3 Replies View Related

MDF File Still Exists After DROP TABLE

Nov 5, 2005

Hello,Using Enterprise Manager, I deleted from my database the only tablethat contained records (Right-Click on Table, Choose "Delete Table").My expectation was that the LARGE .mdf file would be reduced to minimalsize (at least as small as the Northwind MDF). However, it's still 4+Gig in size!! (Northwind is 2.62 MB). This is a problem, bc I deletedthe table in order to recapture hard drive space.NOTE: I already tried using Shrink Database in EM. This significantlyreduced the size of the .mdf file from its original (i.e. pre-delete)size of 38 Gig to present size of 4 Gig.What can I do to further reduce the size of the MDF file?Thank you.

View 1 Replies View Related

Transact SQL :: Drop A Temp Table If It Exists?

Jan 21, 2010

What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end

View 5 Replies View Related

If Exists Drop Index

Feb 8, 2008

I'm trying to drop indexes if they already exist then recreate them as needed, however, I'm getting an error if the index does not exist and I thought that's what the "IF Exists" statement was for.

syntax:
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no') DROP INDEX accounts.idx_acct_no
go
create index [idx_acct_no] ON [dbo].[accounts] ([acct_no])
go

Error when index does not exist:
Server: Msg 3703, Level 11, State 7, Line 1
Cannot drop the index 'accounts.idx_acct_no', because it does not exist in the system catalog.

Is there anyway to not get this error?

View 13 Replies View Related

How To Drop An Index If Exists

Feb 24, 2007

Hi all,

I come from MySQL.

In sqlserverce

I'm looking for an equivalent command..to

"DROP INDEX IF EXISTS inventory.idxItems"

or somthing similar so that I do not get an error or crash the C# app when I try to drop a nonexisting index..

also for droping a table.

View 1 Replies View Related

If Exists DROP PROCEDURE/VIEW

May 20, 2007

how can I drop a propcedure or a view without error for MS SQL 2000/2500

for a table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]


and for a procedure

IF EXISTS ???
DROP PROCEDURE [dbo].[SP_myTABLE_Count]

thank you for helping

View 4 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0

Appreciate your help.

View 3 Replies View Related

Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE

Nov 21, 2006

For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View 6 Replies View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

Transact SQL :: If Not Exists Some ID In One Table Then Insert ID And Description In Another Table

Jul 14, 2015

I need to find out if a Transaction ID exists in Table A that does not exist in Table B.  If that is the case, then I need to insert into Table B all of the Transaction IDs and Descriptions that are not already in. Seems to me this would involve If Not Exists and then an insert into Table B.  This would work easily if there were only one row.  What is the best way to handle it if there are a bunch of rows?  Should there be some type of looping?

View 12 Replies View Related

How To See If Value Exists In Table

May 7, 2007

I have a textbox1 control where a zip code is entered.  What is the most efficient way in C# code behind to see if  that zip code exists in tblZipCode?  I only need to know if it is there, do not need to know how many occurances.

View 7 Replies View Related

Exists Table

Jun 10, 2008

HI,
having a problem with a data loading from 2005 to 2000. Im using export & import wizard. But the problem comes when the user drops a table on 2005. Anyway to check if table exista then use the E&I to load the data.
I cant use the BCP as xp_shellcmd is disable.

View 20 Replies View Related

Delete Or Drop Table Then Create Table

Mar 14, 2007

which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.

View 7 Replies View Related

Check Id Exists In Sql Table?

Jan 17, 2008

Hi all,I am in the process of creating a page that checks to see if a particular user exists in the database and if it does then send to welcome.aspx if not then accessdenied.aspx.  The userid is requested from the query string.I have done some research and cannot find anything directly related, I have tried to add bits of code into what i think is the right place but I dont think what i am doing is correct. Can someone help and show me where my code is going wrong? Also is there a better/more efficient way to do what I am doing?Thanks in advance. default.aspx.csusing System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {        string UserID = Request.QueryString["uid"];        //string TransferPage;        if (UserID != null)                {            //initiate connection to db            SqlConnection objConnect = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString);            string sql = "SELECT COUNT(*) FROM members WHERE UserID = '" + UserID + "'";            SqlCommand query = new SqlCommand(cmd, test);            int count = (int)query.ExecuteScalar();            int aantal = -1; // some default value if can't insert record            if (count == 0) // no existing record for username            {                Server.Transfer("accessdenied.aspx");            }            else            {                Session["UID"] = UserID;                Server.Transfer("welcome.aspx");                            }                    }        }} 

View 7 Replies View Related

Temp Table Exists

Sep 14, 2000

What is the best way to programmatically determine if a temp table exists? In 6.5, I would use

IF EXISTS(SELECT * from tempdb..sysobjects where id = object_id('tempdb..#MyTable') and type = 'U')

But now that it is strongly discouraged to code against system tables, how could I re-write this statement?

The proper way to check the existence of a table would be:

IF OBJECTPROPERTY(object_id('tablename'), 'IsTable') = 1

However, to get this to run for a temp table, I think you'd have to change the database context to tempdb and then back to your database. That doesn't seem efficient.

I could use

IF object_id('tempdb..#MyTable') IS NOT NULL

But that's not guarenteeing that it's a table, right?

Any ideas?

View 5 Replies View Related

Determining If A Table Exists

May 2, 2000

How do I find out if a temporary table named '##test' exists? I have a stored
procedure that creates this table and if it exists another stored procedure
should do one thing, if it does not exist I want the SP to do something else.
Any help as to how I can determine if this table exists at the current time
would be greatly appreciated.

Thanks in advance for you help,
Jon

View 6 Replies View Related

HELP!!... How To Find Out If A Table Exists

Aug 10, 1999

I need a snippet of code that will determine wether or not a table exists in a database...

thanx

View 2 Replies View Related

Check If The Table Exists

May 20, 2008

Hi,

This my first time using the link server to read the files directly from within SQL2005, so issues the following link:-

EXEC sp_addlinkedserver
@server = N'MYVFPSERVER', -- Your linked server name here
@srvproduct=N'Visual FoxPro 9', -- can be anything
@provider=N'VFPOLEDB',
@datasrc=N'"C:PROGRAM FILESMICROSOFT VISUAL FOXPRO 9Samplesdata estdata.dbc"'

After that i can open query and do the import issues, but how can check if the table exists before issues the query.

Best regards

View 2 Replies View Related

Truncate Table If Exists

Jan 26, 2006

Hi,I am trying to create a script that deletes transaction tables and leavesmaster data like customer, vendors, inventory items, etc. How can I useTRUNCATE TABLE with an Exists? My problem is I have 200+ tables, if Isimply use a list like:truncate table01truncate table02truncate table03....I get errors if the table does not exist and have to manually run thetruncate statements. Some tables may not exist if that part of the app isnever used. I'm trying to make a list of all tables that could existwithout it erroring out 50+ times.Thanks in advance.

View 2 Replies View Related

How To Check If A Single Value Exists In A Table

Sep 14, 2006

What’s the easiest way to check if a single value exists in a table column?  I’m building a simple login page, and I want to get the username and check if it exists in my Users table. Here’s my SQL: SELECT UserID FROM UsersWHERE UserID = "admin" Could someone give me code to check for “admin” in my UserID column?    Here’s some code I tried, using my SqlDataSource, but it returns an error “Could not load type 'System.Data.OleDb'”     protected void Button1_Click(object sender, EventArgs e)    {        // Retreive the results from the SqlDataSource as a DataReader        OleDbDataReader reader = (OleDbDataReader)              SqlDataSource1.Select(DataSourceSelectArguments.Empty);         // Read in the value        if (reader.Read())        {             }                     // Close the reader        reader.Close();             } I don’t have to use the SqlDataSource, but originally thought it might be easier. I know how to use SqlDataSource to fill a whole GridView but this is different.  

View 2 Replies View Related

How To Check If A Value Exists In Another Table And Display The Ones That Do Not.

Jun 17, 2008

Basically, i am still relatively new to ASP.net and SQL. And i have the following query.
I have a skills table to which the user enters their skills using the following fields: Skillcatagory, SKill, Current Level, Target Level, target date and comments and the serial of the user.
I need to check via our staff table, which people have had a skill entered for them. And then produce a report on who has not had a skill entered for them.
This table has a serial of the user column aswell which is unique.
If there is more information that i can give you to assist me, please ask me.
 You help would be greatly appreciated.

View 4 Replies View Related

How Do I Know If A Table Exists In A SQL Server Database?

Jul 11, 2005

I need to do some processing but only if a specific table 'table1' exists in the SQL Server database. How can I check if this  'table1' exists using  either ADO.Net code or  SQL Server query?

View 12 Replies View Related

Checking A Table To See If A Record Already Exists

Sep 19, 2007

I've got two tables, one containing a list of company names(approx 10,000 records), the other containing a list of company employees (approx 30,000 records) joined by the CompanyID column.

I have a third table (approx 700 records) containing new employees to be added to the employee table. Each record in this table has the employees details plus the name of their company.

I want to write a query that will check each row in the third table to see if
a) the employee exists in the Employees table
b) the company exists in the Companies table and
c) the employee is listed under the correct company

The query should also handle any combination of the above. So if the company doesn't exist but the employee does, create the company on the companies table and update the appropriate record on the employees table with the new CompanyID etc. etc.

Oh, forgot to mention. The company names in the third table won't be exactly the same as the ones in the Company table so will need to use CharIndex.

Anybody got any ideas?

View 4 Replies View Related

Whether A Column Exists In My Table?(Urgent)

Feb 4, 2004

Hi

Can anybody tell me how can I find whether a column exists in my table or not???

Piyush

View 12 Replies View Related

How To Get Prices From Table Where Date Not Exists

Feb 17, 2013

I have 2 test tables one for stock and one for prices.

I need to select all the rows from Stock but also the price on the stock but the price table doesn't always have the date, so I can not do stock date = price date.

What it needs to do is if the Stoc Date isn't in the price table use the price before... this would also have to be able to run on my rows...

-- Create Test Table (not sure if dates USA or UK format on your machine...

CREATE TABLE [dbo].[TheStockLedger](
[EntryID] [int] NULL,
[TheDate] [datetime] NULL,
[StoreCode] [nvarchar](50) NULL,
[Item] [nvarchar](50) NULL,
[ColorCode] [nvarchar](50) NULL,

[code]....

View 10 Replies View Related

Verifing If Exists A Temporary Table

Apr 15, 2004

Hi,

How can I verify if a temporary table called #X, for example, exists in database?

Thanks.

View 10 Replies View Related

SQL 2012 :: How To Know Whether A Particular Table Type Exists

Jun 11, 2015

So I declared this table data type , cool.. It works

CREATE TYPE BP_Data_XXX
as table
(
XXX_VALUE_NUMERIC numeric(19,2),
bp_type VARCHAR(4),
Dt datetime,
ID int IDENTITY(1,1),
MBP numeric(19,2),
CONCEPT_ID VARCHAR(10)
)

Now my question is later how do we check whether this type exists. The following does not work.

if( object_id('BP_Data_XXX') IS NOT NULL )
print 'IT is there '

View 1 Replies View Related

Dynamically Check If A Table Exists

Apr 10, 2008

Hi there,
I want to create an SQL Function that checks if a table exists and returns true or false. I will pass this function a paramter (say @COMPANYID) e.g.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblmyTableName_' + @COMPANYID) and OBJECTPROPERTY(id, N'IsUserTable') = 1)

how would I write this so the query is dynamically executed and I can get a value of true or false back?

View 4 Replies View Related

Verify Dynamically Specified Table Exists

Jul 23, 2005

I need to write a stored procedure to verify that a table exists andalso that the user executing the stored procedure has access to thespecified table.Any user can call this publicly available procedure and pass a databasename, an owner name and a table name as parameters. The procedurereturns success if the table exists and the user has access to it, orfails if he doesn't. Here's a simplified version of what I have, butI'm wondering if there's a better way. Thanks.create procedure dumb asbegindeclare @myError int,@mytable varchar(128),@myquery varchar(128)select @mytable = '[Northwind].[dbo].[sysobjects2]'select @myquery = 'DECLARE @x int SELECT @x = count(1) from ' +@mytable + ' where 1 = 2'exec (@myquery)select @myError = @@ERRORif @myError != 0BEGINRAISERROR ('ERROR: The specified table %s cannot be accessed.', 10, 1,@mytable)RETURN 1endendgo

View 10 Replies View Related

##temp Table Already Exists Problem

Jul 23, 2005

HelloI am using a temp table called ##temp in an SProc but often get themessage that the table already exists. Could this be because the SProcis being run by more than 2 webpages at the same time?Or is it because the sProc has an error and is not getting to the droptable line?I have tried adding a line to test if the object exists and to drop thetable before I create it. If I drop it will it affect another instanceof the sProc that is using the same table name?If so is there a way around this?Many thanksNigel

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved