I need a SQL or TSQL command (not a stored procedure) that will determine if a table exists (TBL_PARAMETERS). The command needs to return a 1 if the table exists or 0 if it dose not exist.
Thanks!
Sean
I have a few stored procedures that need to follow different execution paths, depending on whether they're running in the development/QA environment or production environment. These are on entirely separate networks.
It would seem that the easiest way would be to set up something like an IF statement to control this by examining the Domain name of the SQL Server instance.
It also seems like there should be an easy way to do this with t-sql, but I'm missing it so far. Anyone know how to do this or dealt with this problem in some other creative way?
Thanks,
Dave
Hi,
I'm trying to run a script to add an index to a table. The index might already exist so I want to drop it 1st if it does.
Is there an equivalent to the following that I use for tables for indexes?
if exists (select * from sysobjects where id = object_id('dbo.tblFred') and sysstat & 0xf = 3)
drop table dbo.tblFred
Thanks,
Martin
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.
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedPreviously 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 RelatedHi,
I have 2 tables,
MembersTemp and Organisations
I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this.
Initiallt I only have the ExecID for the MembersTemp table
MembersType table:
ExecID 3013
OrganisationID 4550
Organisation table:
ID 4550 (PK)
Name "Microboff"
Any ideas??
I want to access sqlserver table properties from asp.net .How do i know that a table defined in sqlserver has autoincrement field or not.Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.
I am not being able identify this property from codes.Please help.thank you
Does anybody know of a way to determine the last date/time a table has been accessed (query/update)? I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table. I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience. Any info will help greatly! Thanks!
View 2 Replies View RelatedHi all,
I am looking for a query to determine the exact size of a large table.
Thanks
JJ
Does anyone have a script that analyzes primary and foreign key relationships of tables and produces a suggested load order based upon dependancies?
Thanks,
Fred.
I have a requirement based on 2 parts to determine a row of data in a table through SSIS :
a. Work out the LEG_SEQ_NBR of the VESSEL, VOYAGE and LEG already stored in table1.
To do this join table1 and join table2 through BL_ID column.
b. Work out the 2ndVESSEL, 2ndVOYAGE and 2ndLEG.
Once we have identified the LEG_SEQ_NBR of the VESSEL,VOYAGE, LEG already stored in table1 we need to add '1' to this value and then find that LEQ_SEQ_NBR in table2.
The DDL of table1 and table2 along with the test data are as below:
CREATE TABLE [dbo].[table1](
[BL_ID] [decimal](10, 0) NOT NULL,
[VESSEL] [nvarchar](10) NULL,
[VOYAGE] [nvarchar](12) NULL,
[LEG] [nchar](3) NULL,
[Code] ....
For Example:
BLNO = 21 and BL_ID = 1
Current Vessel/Voyage/Leg in table1 = CEUR 032E E
LEG_SEQ_NBR of this Vessel/Voyage/Leg in table1 = '1'
Therefore if we add '1' to this value we'd be looking for LEG_SEQ_NBR '2' as the 2ndVESSEL,2ndVOYAGE,2ndLEG.
In this case that would = MARB 794S S
How this can be worked out through an SSIS job ?
I would need to rewrite SQL code to determine that id is unique in the Customer table.
My two tables are:
CREATE TABLE CUSTOMER(
[CUSTNO] varchar(5) NOT NULL,
[ID] CHAR(9) NOT NULL,
[NAME] VARCHAR(128) NOT NULL,
[ADDRESS] VARCHAR(128) NOT NULL,
[DATEOFBIRTH] DATE NOT NULL,
[Code] ....
How, other than by doing the addition on my own, can I detemrine the length of a row in a table?
TIA,
barkingdog
Good Morning
Is There anyway to determine the table you are referencing in a trigger's inserted / deleted tables?
i.e would this be possible
print @Table_Name + " was Updated"
Where @Table_Name is the table on which the trigger is created
(bad example I know but you get the idea)
Any input will be appreciated.
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 Relatedcode that I can use to determine if a certain table has been accessed in the past?
View 6 Replies View RelatedHi all,
I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.
I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.
Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?
Thanks,
-Mike
Hi! I am new to SQL Server... looking for some veteran assistance.
"Data Integrity Report"
I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).
The cursor/report needs to have the following columns:
Ordinal_Position (I.E. Column number)
Column_Name
Number Of Blank Rows (how many missing values for this column in this table)
Difference (Between total rowcount and population of this column)
Data_Type
Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?)
Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx)
The report should look like this (for a table with 100 rows):
Col Num Col Name # Blanks Difference Data Type Col Length Sample Data
1 Name 12 88 varchar 30 Sally Smith
2 Address 34 66 varchar 45 123 Main St Apt 45
3 Acct_ID 0 100 varchar 4 AB12345
Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).
Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.
Sorry for the long post, and thanks in advance!
John
Hi guys,
If I use the script below, where can I find the table "myTable"?
(I need to see the results of the query)
Thanks in advance,
Aldo.
Code Snippet
SELECT MyTable.MyField
INTO MyTable
FROM MyTable
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 RelatedHI,
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.
Does anyone know how to rename a table with TSQL? I want to automate the renaming of a table.
-Matt
How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.
View 2 Replies View RelatedHi 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 Relatedi am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi 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 View RelatedWhat 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?
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
I need a snippet of code that will determine wether or not a table exists in a database...
thanx
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
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
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 RelatedGood afternoon one and all,
I am trying to write a query that will use tables in more than one SQL database (on the same server). How do refer to the table? I have tried dbasename.tablename.fieldname but I don't think that's working.
TIA for any and all help
Gurmi