Hello, there,
I am trying to find out if a table is used by any of the stored
procedures or functions.
I can generate all the scripts and look for it. But is there an easy
way?
THX
John
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
Im working on a db library and I have everything working, what Im wanting to do is though is add a method that can check a connection string to make sure it is actually working. Right now, I have it doing a simple select * query to a particular table and returning true or false if an exception is caused. But I want to make the library as generic as possible, so is there another way to test teh connection string and tell if its working or not?Thanks,Cedric
View 1 Replies View RelatedI have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?
Legal Name ////////////////////////////////////// Legal_Entity_Struct
S & H Farm Supply, Ltd.////////////////////////////Company
F.M.Abbott Power Equipment,Co.///////////////Company
Ray's Dixie Chopper, Inc.////////////////////////// Company
Alexander, Justin N. ///////////////////////////////// Company
Alameda Power Equipment, Inc.//////////////// Company
[Code] .....
Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?
thanks
My contract table DDL:
************************************************** ***
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
My insert stored procedure is:
************************************************** *****
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as
if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end
if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’
commit transaction
return 0
go
I need to create a function that will check data inputted by a user into a column anc check for special charaters. If any of these exist then block the insert.
Any help will be appreciated!
Paul
Hi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
can sql server do this ?
table 1 that check table 2 and adding missing dates
this my employee table
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
-----------------------------------------------------------------------------------
and i need to see the missing dates lkie this
in table 2
------------------------------------------------------
table 2 (adding missing dates with zero 0)
table Employee_all_month
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 0
12345678 12/04/2007 0
12345678 13/04/2007 0
12345678 14/04/2007 0
12345678 15/04/2007 0
12345678 16/04/2007 0
12345678 17/04/2007 0
12345678 18/04/2007 0
12345678 19/04/2007 0
12345678 20/04/2007 0
.................................and adding missing dates with zero 0 until the end of the month
.................................
12345678 31/04/2007 0
98765432 01/04/2007 0
98765432 02/04/2007 0
98765432 03/04/2007 0
98765432 04/04/2007 0
98765432 05/04/2007 0
98765432 06/04/2007 0
98765432 07/04/2007 0
98765432 08/04/2007 0
98765432 09/04/2007 0
..............................and adding missing dates with zero 0 only whre no dates in this month
.......................
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
TNX
Hi,I have two databases called DB1 and DB2. DB1 has a table called table1 and DB2 has table2.I want to write one SP into the DB1, that SP will check whether table2 into the DB2 is exists or not, how do I do it? Any help?I know if it is into the same database then,IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[table2]') and OBJECTPROPERTY(ID, N'IsTable') = 1)-- then do some thingI tried replacing "dbo.sysobjects" with "DB2.dbo.sysobjects", but no luck.Any Help???
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 RelatedHi,I want to check that Is there row exists in table or not.Please correct me. Dim conn As SqlConnection Dim comm As SqlCommand Dim reader As SqlDataReader Dim connstring As String connstring = ConfigurationManager.ConnectionStrings("iharyana").ConnectionString conn = New SqlConnection(connstring) comm = New SqlCommand("select * from test where username=@username", conn) comm.Parameters.Add("@username", Data.SqlDbType.VarChar, 20) comm.Parameters("@username").Value = uname conn.Open() reader = comm.ExecuteReader While reader.Read If reader.Item("username").ToString = "" Then Response.Redirect("http://www.iharyana.com") End If End While reader.Close() conn.Close()
View 1 Replies View RelatedHi...
Is there any way to check previous row in SQL Query?
I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes
I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.
Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD
In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.
Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.
Hi,
Could someone shed some light on the best way to check a table recordcount before run of DTS pkg.
We have a table that is populated in MS SQL from an Oracle database, and the contents of that table are the basis of a DTS package that we run.
Sometimes Oracle transfer bombs out and the table is empty but our process runs anyway. It screws everything up.
Can we check the recordcount of a table on the server, and if the recordcount > 1 then the process runs, if not, then the process aborts?
Thanks,
Dominic
Hello, everyone:
I got a new database from client. How to check which tables are seed table and truncatable? Thanks
ZYT
Hi - please excuse my newness to this. I have a database with several tables and one of them is causing my application to lag really bad. I figure there is either not enough space or something is just wrong in general and i don't know what. Does SQL Server have a shortcut or easy way to test a table in the database?
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 can check the existency of the attribute in particular table. But is it possible for me to check whther my table is exist or not in that particular database?
I am using JSP
Please help!
thank you
Hi,
i wanna know how can i check a table has datas inside, because i wanna use delete from table and if table is empty i dont wanna run this statement.
thanks in advance
Hi SQL Professionals ,
I have a requirement like this,
I have a Live Database as well as the Test database with the same Definition
now i need to write a SQL to check identity of these two database tables,
i mean i need to check if the Test Database has got the Same Table definition as Live Database table definition ?
In the same way how do i check for the Stored peocedures ?
how do i write a SQL for this ?
regardssuis
i have large ssis package which is scheduled daily. The package uses a table on sql server as source and because the execution time is pretty long i just want to execute the whole package only if the data in the source table has changed since the last execution. unfortunatley in the source table there is no timestamp or something like that available. so i thougt about querying some logging information. is there a system table where the time of the last table transaction is stored?
View 7 Replies View RelatedHi all!
I need to check data changes in some tables from specified date. Can it be done without triggers for each table?
SQL Server Management Studio always says "Data was changed" if another user updates data and you try to update old version. How it checks data modification date? I found only this:
USE [ScheduleDB]
GO
SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TTest]') AND type in ('U')
but it can only show structure modification date.
I'm using SS 2012.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".
The input table can have several thousand rows.
I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique
EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View 2 Replies View RelatedHere is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
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 RelatedBasically, 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.
Hi..
I got 10 Tables with data in it for 100 Loans. The data can be Good and Bad .....
I had a Update Proc which Updates the 10 tables with the Good data what ever i pass...for this 100 Loans
Tha Proc will update the existing data in all tables whether it is Good or Bad Data.
when the updating is completed I want to know what are the Loans that were updated where there is a Bad Data in atleast one Field of the 10tables.
I don't want to check field by field in each table.... if there is a bad data and my proc updated with a Good Data i want to know that Loan.
Can any one has an idea on this,,,,
Thanks
Bob
Within the execution of a t-sql script how do I check for the existance of a temporary table associated with the session or a global temporary table?
My understanding is that the table name doesn't get placed in the current database sysobjects table - it goes into tempdb. But the object name is cryptic (so as to be unique) and I see no way of associating it with the current session (@@SPID).
Thanks,
Fred
I have a question about a table with triggers or maybe a check constraint.I have the following create tables:
create table bid(
seller char(10) not null,
item_nummer numeric(3) not null,
)
create table Item(
startprice char(5) not null,
description char(22) not null,
start_date char(10) not null,
end_date char(10) not null,
seller char(10) not null,
item_nummer numeric(3) not null,
)
What i'm trying to make is this trigger/constraint: colomn "seller" from table Item will get NULL as long as systemdate is > start_date and end_date, then it will get the value from seller from table bid on the same item_nummer in both table).
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?
hello everybody,
i have one problem, I want to check the database table modified or not for every five minutes , how can check? please help me
Senthil
I have two tables table1 and table2. I want to check a value from table1 against 4 different columns in table 2. What would be the most optimized way to do this. I came up with this SQL but it runs forever.
select * from table1 a
where
(a.id in (select orig_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select benef_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select send_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select rcv_id from table2 where exctn_dt >= '01-OCT-14'));