Field Exists?

I'm looking for the best way (and most efficient) to check to see if a field
exists.
I have the following, but am not sure if it's the best way.
IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
AND Name='Fieldname')
Is there a better way?
Also curious if there's a syntax for doing it that works with more than just
SQL Server (possibly MySQL and/or Oracle)?Generic != most efficient.
The query you posted seems OK. Note that if you have a view named tablename
with a column named
Fieldname, you would get a hit. Same if you have a stored procedure named ta
blename with a parameter
named Fieldname. So consider filtering on xtype column as well.
If you want generic, use the INFORMATION_SCHEMA.COLUMNS view. This is ANSI S
QL standard, if the
other RDBMS follows the standard, the same query should work. This is probab
ly less efficient, so
check the view definition (it is in master for SQL Server 2000) and see what
it looks like.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:471B3ADA-139D-4FA8-919C-7DE096F8DDF2@.microsoft.com...
> I'm looking for the best way (and most efficient) to check to see if a fie
ld
> exists.
> I have the following, but am not sure if it's the best way.
> IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
> AND Name='Fieldname')
> Is there a better way?
> Also curious if there's a syntax for doing it that works with more than ju
st
> SQL Server (possibly MySQL and/or Oracle)?
>|||Another option is to use the metadata functions like COL_LENGTH or
COLUMNPROPERTY. Use any argument and if it returns NULL then you can
conclude the column does not exist. See SQL Server Books Online for the
various arguments you can use for these functions.
Anith|||"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:471B3ADA-139D-4FA8-919C-7DE096F8DDF2@.microsoft.com...
> I'm looking for the best way (and most efficient) to check to see if a
> field
> exists.
> I have the following, but am not sure if it's the best way.
> IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
> AND Name='Fieldname')
> Is there a better way?
> Also curious if there's a syntax for doing it that works with more than
> just
> SQL Server (possibly MySQL and/or Oracle)?
>
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.TABLE_NAME = 'tablename'
AND c.COLUMN_NAME = 'columnname'
AND t.TABLE_TYPE = 'BASE TABLE'
You can use this query. It will ensure that you don't have a conflict with
a view as well.
Rick Sawtell
MCT, MCSD, MCDBA

Feedback Requested - What Content Do You Want to see for SQL Server Express?

We’re in the process of assessing the content that exists to help people learn and use SQL Server Express. This includes demos, tutorials, whitepapers, Webcasts, Starter Kits, and any other type of content you could imagine. So this is a great chance to TELL US WHAT YOU WANT! We’ll integrate your feedback and make sure that we try and address the most requested areas of content. Please be as specific as you can.

Hi Mark,

I would like to have a demo that shows me how make a diagram from that data in a SQL Server table.

Greetings from Germany,

VBFan

|||I think a crash course in SQL with examples would be great. Also a quick overview of database design and normalization theory could help a beginner get started on the right track.|||I'm looking for an app that will track all computers in my institution. It will need to track them by company property number, and also contain computer name, MAC Address, special software, sensitive functions, etc. I will need to be able to create reports from this data.|||

For very new users some sort of hand-holding for the early stages would be useful. Once you've installed SQL Server Express, it runs but there's no front end and it's not at all obvious how you talk to the beast if your only experience of databases is Access or similar. This is made slightly more difficult in the case of SQL Server Express because BOL tells you that the command sqlcmd will connect you, but in fact it won't -- you need to use sqlcmd -S .\sqlexpress if you accepted the default installation (which is what a raw newbie is going to do).

I think it'd be nice to have something that told you exactly how to create a database (with, say, just two related tables), add data to it, query it, delete data, and then delete the whole database. The database doesn't have to be at all useful: the problem for newbies is finding out how to make things happen and then check that those things really did happen.

Also, something that took you through how the roles work, so you can make sure you're the dbadmin on your database, and get a grasp of how to set up users who're less privileged.

Oh, and maybe something about how to avoid getting log files that seem to get bigger and bigger...

|||

M M Mason wrote:

For very new users some sort of hand-holding for the early stages would be useful. Once you've installed SQL Server Express, it runs but there's no front end and it's not at all obvious how you talk to the beast if your only experience of databases is Access or similar. This is made slightly more difficult in the case of SQL Server Express because BOL tells you that the command sqlcmd will connect you, but in fact it won't -- you need to use sqlcmd -S .\sqlexpress if you accepted the default installation (which is what a raw newbie is going to do).

You can use SQL Management Studio Express for GUI based server management.

|||

Mark,

I am a fresh newbie with hardly any comprehension towards setting up and congifuring SQL Server 2005 express edition and looking through these forums, all I really see are advanced topics and out-of-my-league for now sort of information. I need to know how to set up and configure a SQL Database. I'm not even sure if I have one, and if it doesn't come with SQL Server xpress edition then I doubt I do. But as of now, I'm lost from the very start. I'm a quick learner though, so with this information for setting up and configuring a database I might could pick up on SQL's logic and get a deeper understanding of its programming.

Hope you respond and if not, HOPE SOMEONE DOES!

Thanks,

Michael.

Feedback Requested - What Content Do You Want to see for SQL Server Express?

We’re in the process of assessing the content that exists to help people learn and use SQL Server Express. This includes demos, tutorials, whitepapers, Webcasts, Starter Kits, and any other type of content you could imagine. So this is a great chance to TELL US WHAT YOU WANT! We’ll integrate your feedback and make sure that we try and address the most requested areas of content. Please be as specific as you can.

Hi Mark,

I would like to have a demo that shows me how make a diagram from that data in a SQL Server table.

Greetings from Germany,

VBFan

|||I think a crash course in SQL with examples would be great. Also a quick overview of database design and normalization theory could help a beginner get started on the right track.|||I'm looking for an app that will track all computers in my institution. It will need to track them by company property number, and also contain computer name, MAC Address, special software, sensitive functions, etc. I will need to be able to create reports from this data.|||

For very new users some sort of hand-holding for the early stages would be useful. Once you've installed SQL Server Express, it runs but there's no front end and it's not at all obvious how you talk to the beast if your only experience of databases is Access or similar. This is made slightly more difficult in the case of SQL Server Express because BOL tells you that the command sqlcmd will connect you, but in fact it won't -- you need to use sqlcmd -S .\sqlexpress if you accepted the default installation (which is what a raw newbie is going to do).

I think it'd be nice to have something that told you exactly how to create a database (with, say, just two related tables), add data to it, query it, delete data, and then delete the whole database. The database doesn't have to be at all useful: the problem for newbies is finding out how to make things happen and then check that those things really did happen.

Also, something that took you through how the roles work, so you can make sure you're the dbadmin on your database, and get a grasp of how to set up users who're less privileged.

Oh, and maybe something about how to avoid getting log files that seem to get bigger and bigger...

|||

M M Mason wrote:

For very new users some sort of hand-holding for the early stages would be useful. Once you've installed SQL Server Express, it runs but there's no front end and it's not at all obvious how you talk to the beast if your only experience of databases is Access or similar. This is made slightly more difficult in the case of SQL Server Express because BOL tells you that the command sqlcmd will connect you, but in fact it won't -- you need to use sqlcmd -S .\sqlexpress if you accepted the default installation (which is what a raw newbie is going to do).

You can use SQL Management Studio Express for GUI based server management.

|||

Mark,

I am a fresh newbie with hardly any comprehension towards setting up and congifuring SQL Server 2005 express edition and looking through these forums, all I really see are advanced topics and out-of-my-league for now sort of information. I need to know how to set up and configure a SQL Database. I'm not even sure if I have one, and if it doesn't come with SQL Server xpress edition then I doubt I do. But as of now, I'm lost from the very start. I'm a quick learner though, so with this information for setting up and configuring a database I might could pick up on SQL's logic and get a deeper understanding of its programming.

Hope you respond and if not, HOPE SOMEONE DOES!

Thanks,

Michael.

Feedback Requested - What Content Do You Want to see for SQL Server Express?

We’re in the process of assessing the content that exists to help people learn and use SQL Server Express. This includes demos, tutorials, whitepapers, Webcasts, Starter Kits, and any other type of content you could imagine. So this is a great chance to TELL US WHAT YOU WANT! We’ll integrate your feedback and make sure that we try and address the most requested areas of content. Please be as specific as you can.

Hi Mark,

I would like to have a demo that shows me how make a diagram from that data in a SQL Server table.

Greetings from Germany,

VBFan

|||I think a crash course in SQL with examples would be great. Also a quick overview of database design and normalization theory could help a beginner get started on the right track.|||I'm looking for an app that will track all computers in my institution. It will need to track them by company property number, and also contain computer name, MAC Address, special software, sensitive functions, etc. I will need to be able to create reports from this data.|||

For very new users some sort of hand-holding for the early stages would be useful. Once you've installed SQL Server Express, it runs but there's no front end and it's not at all obvious how you talk to the beast if your only experience of databases is Access or similar. This is made slightly more difficult in the case of SQL Server Express because BOL tells you that the command sqlcmd will connect you, but in fact it won't -- you need to use sqlcmd -S .\sqlexpress if you accepted the default installation (which is what a raw newbie is going to do).

I think it'd be nice to have something that told you exactly how to create a database (with, say, just two related tables), add data to it, query it, delete data, and then delete the whole database. The database doesn't have to be at all useful: the problem for newbies is finding out how to make things happen and then check that those things really did happen.

Also, something that took you through how the roles work, so you can make sure you're the dbadmin on your database, and get a grasp of how to set up users who're less privileged.

Oh, and maybe something about how to avoid getting log files that seem to get bigger and bigger...

|||

M M Mason wrote:

For very new users some sort of hand-holding for the early stages would be useful. Once you've installed SQL Server Express, it runs but there's no front end and it's not at all obvious how you talk to the beast if your only experience of databases is Access or similar. This is made slightly more difficult in the case of SQL Server Express because BOL tells you that the command sqlcmd will connect you, but in fact it won't -- you need to use sqlcmd -S .\sqlexpress if you accepted the default installation (which is what a raw newbie is going to do).

You can use SQL Management Studio Express for GUI based server management.

|||

Mark,

I am a fresh newbie with hardly any comprehension towards setting up and congifuring SQL Server 2005 express edition and looking through these forums, all I really see are advanced topics and out-of-my-league for now sort of information. I need to know how to set up and configure a SQL Database. I'm not even sure if I have one, and if it doesn't come with SQL Server xpress edition then I doubt I do. But as of now, I'm lost from the very start. I'm a quick learner though, so with this information for setting up and configuring a database I might could pick up on SQL's logic and get a deeper understanding of its programming.

Hope you respond and if not, HOPE SOMEONE DOES!

Thanks,

Michael.

Fast Query

Hi,
What's the fastest way of checking if a value exists in a table? How is this
done in a production enviornment? Is it better ot return the result or a 1 o
r
0 as yes or no via an output parameter.
I am currently using
Create Proc test(
@.searchVal nvarchar(10))
as
select id from tablea where id = @.searchVal
ThanksIts better to select the key or something like this that I always choose
select 1 from tablea where id = @.searchVal
and you can assign it to an output parameter or type BIT. Better than
getting a dataset. And in the calling application (with respect to C#.NET
here) use ExecuteScalar() and get the value.
Hope this helps.|||You can also use exists
Create Proc test(
@.searchVal nvarchar(10))
as
if exists (select * from tablea where id = @.searchVal)
select 1
else
select 0
Denis the SQL Menace
http://sqlservercode.blogspot.com/

Failure Details?

Where is, (or even does it exists) the best place to look for some details on when package execution fails if running as a scheduled job. Obviously when you run from the command line or in VS, there is plenty of output detail on progress and on the source of errors, but when you run it as a scheduled job, it just says step 1 failed in the sql server log, and package foo failed in the NT application log . Is there anywhere to find this info or do we need to build error traps into the package to write stuff out somewhere?

THX

Davepackage logging looks like a candidate?

http://support.microsoft.com/kb/918760|||

drmcl wrote:

package logging looks like a candidate?

http://support.microsoft.com/kb/918760

Its the ONLY candidate.

You should also be aware of this:

Job History and Job Step Sub-Systems
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)

-Jamie