An easy way to do this is to use the INFORMATION SCHEMA objects. They provide a handy way to get at the tables/views/etc inside the database..
Code Snippet
USE MyDatabase;
SELECT * FROM INFORMATION_SCHEMA.VIEWS
If you need to look for anything else inside the database, there are a bunch of others:
http://msdn2.microsoft.com/en-us/library/ms186778.aspx
|||I really appreciate that. Thank you.An easy way to do this is to use the INFORMATION SCHEMA objects. They provide a handy way to get at the tables/views/etc inside the database..
Code Snippet
USE MyDatabase;
SELECT * FROM INFORMATION_SCHEMA.VIEWS
If you need to look for anything else inside the database, there are a bunch of others:
http://msdn2.microsoft.com/en-us/library/ms186778.aspx
|||I really appreciate that. Thank you.An easy way to do this is to use the INFORMATION SCHEMA objects. They provide a handy way to get at the tables/views/etc inside the database..
Code Snippet
USE MyDatabase;
SELECT * FROM INFORMATION_SCHEMA.VIEWS
If you need to look for anything else inside the database, there are a bunch of others:
http://msdn2.microsoft.com/en-us/library/ms186778.aspx
|||I really appreciate that. Thank you.How can I check if a temporary table exists in the current context?
With normal tables I'd do a
EXISTS ( SELECT name FROM sysobjects
WHERE name='myTableName' AND type='U')
However, I can't do that with a temporary table. I'd have to go look at the sysobjects table in the tempdb database.
The problem is that for temporary tables, a suffix is added to the name to make it unique for each scope. I can't change the WHERE clause to name LIKE 'myTableName%' because this would return true if a temporary table with the same name exists in a different scope.
Any ideas?
Carlos
You can try the following
IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END
CREATE TABLE #MyTempTable
(
ID int IDENTITY(1,1),
SomeValue varchar(100)
)
GO
I am building a string of the form:
FirstName LastName, FirstName LastName, FirstName LastName,...
and I would like to only add the current name if it isn't already in the string.
The reason I am doing this is because the table I'm working on has multiple entries for the same key but with different values for one of the columns.
The problem is I can't figure out how to check if current name already exists in the name list string that was previously built.
I appreciate your replies. Thank you.
The direct answer is,
CharIndex('Your Searching String', 'Your Main string)
If the result is NON ZERO Then the value is already exists.
Example,
Select Case When CharIndex('Three Four','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --Yes
Select Case When CharIndex('Three Five','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --No
NOTE:
But it is really bad idea to keep the multiple entry values as columns.
Better you can have a one more table where you can make a new row for each names.
The design which you are using is not recommended.
|||
I am pulling data from several tables and in one of them for some reason ( it might be a bug in how that table is generated or in how inserts are being made) I have multiple values for the same column for the same key. Which is generating multiple rows in my query. I cannot change that design. I can only work with it.
Thanks for your answer.
|||hi you can try this options..SELECT *
INTO #Names
FROM (
SELECT 'John' AS FirstName, 'Doe' AS LastName, 1 AS OtherColumn UNION ALL
SELECT 'John' AS FirstName, 'Doe' AS LastName, 2 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 3 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn UNION ALL
SELECT 'Princess Quamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn
) Names
SELECT *
FROM #Names
-- option 1
DECLARE @.Names varchar(100)
SET @.Names = ''
SELECT @.Names = @.Names + FirstName + ' ' + LastName + ', '
FROM (
SELECT DISTINCT
FirstName
, LastName
FROM #Names
) Names
ORDER BY
FirstName
, LastName
SET @.Names = LEFT(@.Names,LEN(@.Names) - 1)
-- option 2
DECLARE @.Names2 varchar(100)
SET @.Names2 = ''
SELECT @.Names2 = @.Names2 + (CASE WHEN CHARINDEX(FirstName + ' ' + LastName, @.Names2) > 0 THEN '' ELSE FirstName + ' ' + LastName + ', ' END)
FROM #Names
ORDER BY
FirstName
, LastName
SET @.Names2 = LEFT(@.Names2,LEN(@.Names2) - 1)
SELECT @.Names as FromOption1, @.Names2 AS FromOption2
DROP TABLE #Names
If your schema should exist in the form of an .xsd file then you can use the system.io.file object as:
if system.io.file(path\name.xsd).exists then
the exists method will return a true if the file is present.
If this is not what you are looking for, where else might a "schema" exist that you would have to create one? In SQL? There too is the "Exists" keyword in t-SQL, you can, for instance create an SP that would look something like:
[based on the pubs demo db]
CREATE PROCEDURE dbo.MakeEmployeeTable
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[employee] (
[emp_id] [empid] NOT NULL ,
[fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[minit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lname] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[job_id] [smallint] NOT NULL ,
[job_lvl] [tinyint] NULL ,
[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[hire_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
This checks to see if a table is present and if it is not, adds it, with a given schema.
|||I will take the other path and assume you meant relational schema :)
I use:
if schema_id('dog') is null
execute('create schema dog') --exec because 'CREATE SCHEMA' must be the first statement in a query batch.
|||Sorry. I should have mentioned that I am talking about schema in the database; not xml schema. Thanks for the help.|||
if exists(select * from sys.schemas where name = 'myschema')
begin
--ADD YOUR CODE HERE
end
else
begin
--ADD YOUR CODE HERE
--CREATE SCHEMA [myschema] AUTHORIZATION [dbo]
end
If your schema should exist in the form of an .xsd file then you can use the system.io.file object as:
if system.io.file(path\name.xsd).exists then
the exists method will return a true if the file is present.
If this is not what you are looking for, where else might a "schema" exist that you would have to create one? In SQL? There too is the "Exists" keyword in t-SQL, you can, for instance create an SP that would look something like:
[based on the pubs demo db]
CREATE PROCEDURE dbo.MakeEmployeeTable
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[employee] (
[emp_id] [empid] NOT NULL ,
[fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[minit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lname] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[job_id] [smallint] NOT NULL ,
[job_lvl] [tinyint] NULL ,
[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[hire_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
This checks to see if a table is present and if it is not, adds it, with a given schema.
|||I will take the other path and assume you meant relational schema :)
I use:
if schema_id('dog') is null
execute('create schema dog') --exec because 'CREATE SCHEMA' must be the first statement in a query batch.
|||Sorry. I should have mentioned that I am talking about schema in the database; not xml schema. Thanks for the help.|||
if exists(select * from sys.schemas where name = 'myschema')
begin
--ADD YOUR CODE HERE
end
else
begin
--ADD YOUR CODE HERE
--CREATE SCHEMA [myschema] AUTHORIZATION [dbo]
end
I created the following SQL script to check if a record exists:
IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@.LevelName) = LOWER(LevelName)))
Return (1)
ELSE
Return (0)
And I also found in a web page another solution:
IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@.LevelName)
SELECT 1
ELSE
SELECT 0
- Which approach should I use?
- Why "SELECT 1 FROM"?
- And when should I use SELECT or RETURN?
All I need is to know if the record exists ... nothing else.
I will use this procedure on an ASP.NET 2.0 / C# web site.
I am not sure if this important but anyway ...
Thank You,
Miguel
select 1 from table returns a value which is basically the same as selecting a column name when evaluating from the exists function. The difference is that 1 is a constant so the column name does not need to be looked up, and since you do not need the value of the column then select 1 can be used.
using return or select depends on what you are using to call the sql statement. If you use return then you need to look into the calls returns parameters. Using a select , you need to use a scalar or dataset return call. Most people use the select call because those calls are easier to handle but not necessarily more efficient
|||
In EXISTS you can use any of them but the result will be the same (it always look for first occurrence of value selected) I do not know about time of execution but I would prefer something like this
RETURN (CASE
when EXISTS (SELECT LevelName
FROM dbo.by27_Levels
WHERE LOWER(@.LevelName) = LOWER(LevelName))) then 1
else
0
end)
If it will be executed on SQL server and you server is Case insensitive you do not have to use LOWER and it will speed up a little.
Thanks
ozkary:
If you use return then you need to look into the calls returns parameters.
What do you mean to look the calls returns parameters?
Can you point to some info about it?
Thanks,.
Miguel
By default SQL Server is not case sensitive so the LOWER() is not needed.
If the LevelName is a unique key for the table I would avaoid using T-SQL and use a single generic SQL query:
SELECT COUNT(*) FROM dbo.by27_Levels WHERE @.LevelName = LevelName
RETURN ends the execution of the batch T-SQL and SELECT does not. Note any select results not stored in local variables will be output.
|||Some stored procedures could have return parameters they are defined with OUTPUT
create procedure TEST
@.tcParam1 as varchar(100) = NULL,
@.tcOutputParam as varchar(100) = NULL OUTPUT
AS
BEGIN
...
SET @.tcOutputParam= 'result'
END
and if you call it
declare @.oparam as varchar(100)
exec test 'Test valuee', @.oparam OUTPUT
you can get output value from procedure
RETURN always is returned by stored procedure and you can get it like:but it only integer, output parameter can be almost any type
EXEC @.result = test 'Test valuee', @.oparam OUTPUT
Thanks
|||yes, one usually uses parameters to call a stored procedure. Those parameters can have the following directions: INPUT, OUTPUT, RETURN.
To hadle a return value, one needs to add a return parameter to the call:
SqlCommand cmd = new SqlCommand("myProc", myConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters..Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
add block to make the call
if using a reader make sure to close it before trying to read the return parameter
read the return parameter value
string value = cmd.Parameters.item["ReturnValue"].Value.Tostring();
for more info search on ParameterDirection.ReturnValue
I hope this helps.
I have created a formview which I among other things uses to insert new values into a database. What I want to check is if the primary key which is put into the form already exists in the db. If it is I want to get a message to my web page, if not the data can be inserted.
How can I do this?
And if the only way to control this is to create a stored procedure. How do I write such a proc?
If you attempt to insert a duplicate value into a PK field, an exception will be thrown. Why not set it to auto-increment and avoid all of this?
HTH,
Ryan
In SSIS, I need an easy way to see if a file exists, and if not wait for it until a timeout period expires. Here are the options I've discovered, along with the issues I've had:
a) The File Watcher task from www.sqlis.com
This was my first attempt. The task works great, BUT only detects when there is a change on the file. If the file already exists, it keeps waiting which is not the behavior I need.
b) The WMI Event Task
There is very sparce documentation on this event and how to write a WQL query. There are numerous examples of monitoring a folder and if any files appear, cause an event to happen. I need to detect for a specific file. I found maybe one example of this using "PartComponent" but wasn't able to get the sytax right to make it work for me. I also need to access a remote file share using a UNC path (e.g. \\servername\path\file.txt) which I could not get to work.
c) Script Task using the File.Exists() method
I imported the System.IO namespace, and used a File.Exists(\\servername\path\file.txt) with actual success, but am not sure of the best way to continue to wait if the file is not found immediately. I also want to modularize this approach so I can wait for several files simultaneously so was thinking of implementing this script task as a package by itself to accept variables (filepath & timeout period) but need to know if anyone has had success with this approach.
I'm open to suggestions or ways to get options a) and b) to work for my needs.
Thanks!
Kory
Most folks use a special folder that only contains files that need to be processed. That way, you can have workflow that processes any files that exist in the folder without concern for whether it is the right type of file etc. because only the correct file types get dropped there.
Then, you can use the file watcher task effectively because you can have two processing sections in the package, a part that picks up whatever files exist in the folder and then another part that waits for new files to appear.
HTH,
Kirk Haselden
Author "SQL Server Integration Services"
Your scenario descibes basically what we do. There is a single folder with all text files, but with extention of ".flg" These files do not actually contain any information (other than date/time/process) but are only for triggering other processes to began. The file names are associated with the names of tables loaded in our DW.
Unfortunately the flag file process we rely on is out of my control, and is managed by another entprise group within my company. I only have read-only access to monitor a single folder that contains about 100 files, each named corresponding to the table that has become available. I need to check this folder starting 3:00am every morning and continue to monitor it until a specific file appears. The folder is emptied at 3:00pm the next afternoon every day. If the file already exists at 3:00am, this means the table was ready earlier than 3:00am so the process can resume as normal.
So, I am still looking for a solution...
-Kory
|||Why not use the script file task to check if it existing and a file watcher to wait if not there. Some simple workflow should allow this scenario.|||Yeah, script task will allow you to do this. Check out System.IO.File.Exists() static method
-Jamie
|||
The File Watcher Task has been updated to now check for an existing file that matches the criteria. This behaviour optional, with the default being to only look for new or changed files, as with previous versions. This can be controlled by the new FindExistingFiles property.
The current release (1.2.4.55) is fully backwardly compatible with previous versions, just uninstall the old version and then install the new version. It will add the new property on any subsequent package save, or you can force an upgrade within the Solution Explorer tool window, by right-clicking and selecting Reload with Upgrade, although this is not necessary.
File Watcher Task
(http://www.sqlis.com/default.aspx?23)
In SSIS, I need an easy way to see if a file exists, and if not wait for it until a timeout period expires. Here are the options I've discovered, along with the issues I've had:
a) The File Watcher task from www.sqlis.com
This was my first attempt. The task works great, BUT only detects when there is a change on the file. If the file already exists, it keeps waiting which is not the behavior I need.
b) The WMI Event Task
There is very sparce documentation on this event and how to write a WQL query. There are numerous examples of monitoring a folder and if any files appear, cause an event to happen. I need to detect for a specific file. I found maybe one example of this using "PartComponent" but wasn't able to get the sytax right to make it work for me. I also need to access a remote file share using a UNC path (e.g. \\servername\path\file.txt) which I could not get to work.
c) Script Task using the File.Exists() method
I imported the System.IO namespace, and used a File.Exists(\\servername\path\file.txt) with actual success, but am not sure of the best way to continue to wait if the file is not found immediately. I also want to modularize this approach so I can wait for several files simultaneously so was thinking of implementing this script task as a package by itself to accept variables (filepath & timeout period) but need to know if anyone has had success with this approach.
I'm open to suggestions or ways to get options a) and b) to work for my needs.
Thanks!
Kory
Most folks use a special folder that only contains files that need to be processed. That way, you can have workflow that processes any files that exist in the folder without concern for whether it is the right type of file etc. because only the correct file types get dropped there.
Then, you can use the file watcher task effectively because you can have two processing sections in the package, a part that picks up whatever files exist in the folder and then another part that waits for new files to appear.
HTH,
Kirk Haselden
Author "SQL Server Integration Services"
Your scenario descibes basically what we do. There is a single folder with all text files, but with extention of ".flg" These files do not actually contain any information (other than date/time/process) but are only for triggering other processes to began. The file names are associated with the names of tables loaded in our DW.
Unfortunately the flag file process we rely on is out of my control, and is managed by another entprise group within my company. I only have read-only access to monitor a single folder that contains about 100 files, each named corresponding to the table that has become available. I need to check this folder starting 3:00am every morning and continue to monitor it until a specific file appears. The folder is emptied at 3:00pm the next afternoon every day. If the file already exists at 3:00am, this means the table was ready earlier than 3:00am so the process can resume as normal.
So, I am still looking for a solution...
-Kory
|||Why not use the script file task to check if it existing and a file watcher to wait if not there. Some simple workflow should allow this scenario.|||Yeah, script task will allow you to do this. Check out System.IO.File.Exists() static method
-Jamie
|||
The File Watcher Task has been updated to now check for an existing file that matches the criteria. This behaviour optional, with the default being to only look for new or changed files, as with previous versions. This can be controlled by the new FindExistingFiles property.
The current release (1.2.4.55) is fully backwardly compatible with previous versions, just uninstall the old version and then install the new version. It will add the new property on any subsequent package save, or you can force an upgrade within the Solution Explorer tool window, by right-clicking and selecting Reload with Upgrade, although this is not necessary.
File Watcher Task
(http://www.sqlis.com/default.aspx?23)
I tried to search if this had been asked before but didn't found anything so I hope It has not been 100 times before me.
Is it possible to check if a database allready exist in the SQL Server 2005? If for example I want to create a database called "Testing" I first want to check if the database exists before I create it.
Thanks in advance!
Try:
if not exists(select * from sys.databases where name = 'Testing')
create database testing
Hope this helps.
Niels
|||Thank you, that worked great, now I just have to figure out how to check if a table exists in the database =)
|||
You can use the various metadata functions.
if db_id('dbname') is not null
if object_id('object_name', 'U') is not null -- for table
if object_id('object_name', 'P') is not null -- for SP
These are much easier to read and use than queries against system tables/views. See SQL Server 2005 Books Online for more details on the object_id function.
|||The problem with all of your methods for checking table (or object) existence, is that it will only check in the current database for the object.
Here is a SPROC that will check another database for the existence of an object:
CREATE PROCEDURE [dbo].[ObjectCheck_sp]
@.DB SysName,
@.Name SysName,
@.Type nvarchar(1000)
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @.Cmd NVARCHAR(MAX)
Declare @.Out Table ( ResultSet XML )
IF LEFT(@.Type, 1) != '('
SET @.Type = '(' + CHAR(39) + @.Type + CHAR(39) + ')'
Set @.CMD = 'Select * from [' + @.DB + '].sys.objects where name=' + CHAR(39) + @.Name + CHAR(39) + ' and type in ' + @.Type + ' For XML Auto,Type'
Insert into @.Out
EXEC ( @.CMD )
IF EXISTS ( Select *
from @.Out
where ResultSet is not null )
RETURN 1
ELSE
RETURN 0
END -- Procedure: ObjectCheck_sp
An example call is:
Declare @.RetVal INT
exec @.RetVal=ObjectCheck_sp @.DB='master', @.Name='MSreplication_options', @.Type=N'( ''U'', ''V'')'
Select @.RetVal
I tried to search if this had been asked before but didn't found anything so I hope It has not been 100 times before me.
Is it possible to check if a database allready exist in the SQL Server 2005? If for example I want to create a database called "Testing" I first want to check if the database exists before I create it.
Thanks in advance!Try:
if not exists(select * from sys.databases where name = 'Testing')
create database testing
Hope this helps.
Niels|||Thank you, that worked great, now I just have to figure out how to check if a table exists in the database =)|||
You can use the various metadata functions.
if db_id('dbname') is not null
if object_id('object_name', 'U') is not null -- for table
if object_id('object_name', 'P') is not null -- for SP
These are much easier to read and use than queries against system tables/views. See SQL Server 2005 Books Online for more details on the object_id function.
|||The problem with all of your methods for checking table (or object) existence, is that it will only check in the current database for the object.
Here is a SPROC that will check another database for the existence of an object:
CREATE PROCEDURE [dbo].[ObjectCheck_sp]
@.DB SysName,
@.Name SysName,
@.Type nvarchar(1000)
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @.Cmd NVARCHAR(MAX)
Declare @.Out Table ( ResultSet XML )
IF LEFT(@.Type, 1) != '('
SET @.Type = '(' + CHAR(39) + @.Type + CHAR(39) + ')'
Set @.CMD = 'Select * from [' + @.DB + '].sys.objects where name=' + CHAR(39) + @.Name + CHAR(39) + ' and type in ' + @.Type + ' For XML Auto,Type'
Insert into @.Out
EXEC ( @.CMD )
IF EXISTS ( Select *
from @.Out
where ResultSet is not null )
RETURN 1
ELSE
RETURN 0
END -- Procedure: ObjectCheck_sp
An example call is:
Declare @.RetVal INT
exec @.RetVal=ObjectCheck_sp @.DB='master', @.Name='MSreplication_options', @.Type=N'( ''U'', ''V'')'
Select @.RetVal
By using AMO in a script task
HTH
Ovidiu
By using AMO in a script task
HTH
Ovidiu
