Describing a table structure

Hi,
I would like to know the syntax in MS SQL 2K for describing a table, without
having to expand everything in the object browser.
I know in Oracle it is: desc <table_name>;
Thanks,
Jig.
Do you mean
sp_help tblname
"Jig Bhakta" wrote:

> Hi,
> I would like to know the syntax in MS SQL 2K for describing a table, without
> having to expand everything in the object browser.
> I know in Oracle it is: desc <table_name>;
> Thanks,
> Jig.
|||is that the only way?
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Do you mean
> sp_help tblname
> "Jig Bhakta" wrote:
|||This will create a script for it
http://www.mindsdoor.net/DMO/DMOScripting.html
and this will do a database or all databases on a server
http://www.mindsdoor.net/DMO/DMOScriptAllDatabases.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi,
there may be other ways, but this gives the info in more
or less the same format as you get in Oracle. Just make
sure that you select 'Results in grid' from the Query tab.
Jig,
that is the correct format
Mark
[vbcol=seagreen]
>--Original Message--
>is that the only way?
>"Nigel Rivett" wrote:
describing a table, without
>.
>

Desc as column name

Hi,
When I run the following sql statement
CREATE TABLE t1 (Desc varchar(50))
I get an error saying Incorrect syntax near keyword Desc.
However, I can run the same command on other database engines.
Where can I find situations like this where an sql statement will work
on other databases but not SqlServer?
The statement
CREATE TABLE t1 ([Desc] varchar(50))
works fine.
Do you suggest that I enclose column names in square brackets ([]) for
all sql statements (select, insert, update, create, delete etc)?
Regards,
PrakashSince you mention other database engines, you should get into the habit of using the ANSI SQL
compliant way to delimit identifiers: double-quotes and not square brackets.
There's a list in Books Online of all the reserved keywords.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>|||Desc is a reserved keyword in SQL Server. It's short for 'descending' and is
used in syntax such as ORDER BY column_name DESC. If you want to use
reserved words as object or column names, you must delimit the name by
either using [ ] as you show below are by using double quotes " ". Keep in
mind that you will need to delimit this name in every statement in which you
reference the name. You might consider just choosing a longer (and more
descriptive) name such as "Description".
For a list of reserved keywords, see this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms189822.aspx
For more information about delimiting names see this Books Online topic
http://msdn2.microsoft.com/en-us/library/ms176027.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>sql

Derived Tables and joining to them

I am struggling with some syntax. I have created a couple of derived tables
and now want to LEFT OUTER JOIN to them. Can someone help me?
Thanks in advance.
Here's the SQL...
SELECT DERIVE1A.column_1,
DERIVE1A.column_2,
DERIVE1A.column_3,
DERIVE1A.column_4,
DERIVE1A.column_5,
DERIVE2A.column_1,
DERIVE2A.column_2,
DERIVE2A.column_3,
DERIVE2A.column_4,
DERIVE2A.column_5
FROM #tbl_Export tbl_Export,
(SELECT column_1,
column_2,
column_3,
column_4,
column_5
FROM #tbl_Export tbl_export_1
WHERE column_x = 'f') DERIVE1,
(SELECT column_1,
column_2,
column_3,
column_4,
column_5
FROM #tbl_Export tbl_export_1
WHERE column_x = 's') DERIVE2,
LEFT OUTER JOIN DERIVE1 DERIVE1A
ON tbl_Export.key_column = DERIVE1A.key_column
LEFT OUTER JOIN DERIVE2 DERIVE2A
ON tbl_Export.key_column = DERIVE2A.key_column
wnfisbaStart by checking the syntax and examples from SQL Server Books Online.
Based on the sample code you posted, you could re-write it along the lines
of:
SELECT *
FROM tbl t1
LEFT OUTER JOIN
( SELECT col1, col2, ...... ) derived_1
ON t1.key = derived_1.col1
LEFT OUTER JOIN
( SELECT col1, col2, ...... ) derived_2
ON t1.key = derived_2.col2 ;
Anith

derived table

Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.Your syntax looks fine.
Is it not doing what you expect it to?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses whe
re
> program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
to
> do this in a query?
> Please help. Thanks.
>
>
>|||Please post the intended results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.|||I got error:
No column was specified for column 3 of 'DB1'?
what's that mean? Thanks.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
> Your syntax looks fine.
> Is it not doing what you expect it to?
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>|||Is 'mydb' the name of the database you want information about?
program_name refers to the application that is accessing the database.
If so:
The Database is in the field dbid.
Try:
select count(*) from (select hostname, program_name from sysprocesses where
dbid=db_id('mydb') group by hostname, program_name) as DB1
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses
> where program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
> to do this in a query?
> Please help. Thanks.
>
>|||You must have posted a different query than what you are trying to run.
That error is telling you that all columns specified in a derived table need
a name, so that they can be referenced by the outer query.
If you had a query like:
select count(*) from (select hostname, program_name, count(1) from
sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
I'd expect the error. Because the third column does not have a referencable
name for the outer query. It would need to be re-written like:
select count(*) from (select hostname, program_name, count(1) as rowCount
from sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>|||your post is missing the aggregate for DB1, btw
however, this is the problem: all columsn in in a derived table must
have a name, e.g. [using max(spid) for example]
select count(*) from (select hostname, program_name, max(spid) as spid
from sysprocesses where program_name='mydb' group by hostname,
program_name) as DB1
or
select count(*) from (select hostname, program_name, max(spid)from
sysprocesses where program_name='mydb' group by hostname, program_name)
as DB1 (hostname, program_name, spid)
js wrote:
> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>
>|||Thanks Trey.
I got it. need to assign name: count(*) as counters first
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:u3fb5gjFGHA.984@.tk2msftngp13.phx.gbl...
> your post is missing the aggregate for DB1, btw
> however, this is the problem: all columsn in in a derived table must have
> a name, e.g. [using max(spid) for example]
> select count(*) from (select hostname, program_name, max(spid) as spid
> from sysprocesses where program_name='mydb' group by hostname,
> program_name) as DB1
> or
> select count(*) from (select hostname, program_name, max(spid)from
> sysprocesses where program_name='mydb' group by hostname, program_name) as
> DB1 (hostname, program_name, spid)
>
> js wrote:|||Thanks Ryan.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:6026DBD4-29D4-426C-BF58-DCAB853CB8E0@.microsoft.com...
> You must have posted a different query than what you are trying to run.
> That error is telling you that all columns specified in a derived table
> need
> a name, so that they can be referenced by the outer query.
> If you had a query like:
> select count(*) from (select hostname, program_name, count(1) from
> sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> I'd expect the error. Because the third column does not have a
> referencable
> name for the outer query. It would need to be re-written like:
> select count(*) from (select hostname, program_name, count(1) as rowCount
> from sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>sql

Derived Column in CREATE TABME

Hi champs,
I am kicking my self for the syntax for a derived column in a CREATE TABME
-statement.
I just wnat a extra colum that is the result of colum1+colum2
..
CREATE TABLE [dbo].[test](
[colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum3] = [colum1] + [colum2] [nchar](10)
) ON [PRIMARY]
what is the correct syntax?
/many thanksKurlan
CREATE TABLE [dbo].[test](
[colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum3] AS [colum1] + [colum2]
) ON [PRIMARY]
"Kurlan" <Kurlan@.discussions.microsoft.com> wrote in message
news:7D8CBBB0-4538-47C9-833F-450D689879E2@.microsoft.com...
> Hi champs,
> I am kicking my self for the syntax for a derived column in a CREATE TABME
> -statement.
> I just wnat a extra colum that is the result of colum1+colum2
> ..
> CREATE TABLE [dbo].[test](
> [colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum3] = [colum1] + [colum2] [nchar](10)
> ) ON [PRIMARY]
> what is the correct syntax?
>
> /many thanks|||CREATE TABLE [dbo].[test](
[colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum3] as [colum1] + [colum2]
) ON [PRIMARY]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Kurlan" wrote:

> Hi champs,
> I am kicking my self for the syntax for a derived column in a CREATE TABME
> -statement.
> I just wnat a extra colum that is the result of colum1+colum2
> ..
> CREATE TABLE [dbo].[test](
> [colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum3] = [colum1] + [colum2] [nchar](10)
> ) ON [PRIMARY]
> what is the correct syntax?
>
> /many thanks|||Kurlan a écrit :
> Hi champs,
> I am kicking my self for the syntax for a derived column in a CREATE TABME
> -statement.
> I just wnat a extra colum that is the result of colum1+colum2
> ..
> CREATE TABLE [dbo].[test](
> [colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
==> [colum3] = [colum1] + [colum2] [nchar](10)
> ) ON [PRIMARY]
> what is the correct syntax?
>
> /many thanks
The SQL type must not be explicit. It will be implicit by calculating
the SQL type needed by the expression.
Remember that this type of columns are computed while running the SELECT.
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||>> CREATE TABLE [dbo].[test](
>==> [colum3] = [colum1] + [colum2] [nchar](10)
>The SQL type must not be explicit. It will be implicit by calculating
>the SQL type needed by the expression.
If, for some reason, there is a requirement for an explicit data type,
this can be achieved by adding a CONVERT to the expression:
[colum3] = CONVERT(nchar(10), [colum1] + [colum2])
Roy Harvey
Beacon Falls, CTsql

Deploying a managed code trigger

I am working on a managed code trigger using visual studio 2005 and SQL 2005
.
When I try to deploy it I get the following error:
Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility
level of the current database to a higher value to enable this feature. See
help for the stored procedure sp_dbcmptlevel.
I have set the assembly permission level to safe.
The database I am using is one I restored from SQL 2000 into SQL 2005. I
tried setting the datbase compatibility level to 8, and then to 7, and still
get the same error. Any ideas?
Ron Coffee, MCSD> The database I am using is one I restored from SQL 2000 into SQL 2005. I
> tried setting the datbase compatibility level to 8, and then to 7, and
> still
> get the same error. Any ideas?
CLR triggers were introduced in SQL 2005. Have you tried setting the
database compatibility level to 90 (SQL 2005)?
Hope this helps.
Dan Guzman
SQL Server MVP
"Ron_Coffee" <Ron_Coffee@.discussions.microsoft.com> wrote in message
news:181E0D5F-D699-477C-B266-31D2AE5B464E@.microsoft.com...
>I am working on a managed code trigger using visual studio 2005 and SQL
>2005.
> When I try to deploy it I get the following error:
> Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility
> level of the current database to a higher value to enable this feature.
> See
> help for the stored procedure sp_dbcmptlevel.
>
> I have set the assembly permission level to safe.
> The database I am using is one I restored from SQL 2000 into SQL 2005. I
> tried setting the datbase compatibility level to 8, and then to 7, and
> still
> get the same error. Any ideas?
> --
> Ron Coffee, MCSD|||You'll need compatibility level 9 -- SQL 2005 is version 9.0
"Ron_Coffee" wrote:

> I am working on a managed code trigger using visual studio 2005 and SQL 20
05.
> When I try to deploy it I get the following error:
> Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility
> level of the current database to a higher value to enable this feature. Se
e
> help for the stored procedure sp_dbcmptlevel.
>
> I have set the assembly permission level to safe.
> The database I am using is one I restored from SQL 2000 into SQL 2005. I
> tried setting the datbase compatibility level to 8, and then to 7, and sti
ll
> get the same error. Any ideas?
> --
> Ron Coffee, MCSD|||Thanks! That solved it
--
Ron Coffee, MCSD
"Dan Guzman" wrote:

> CLR triggers were introduced in SQL 2005. Have you tried setting the
> database compatibility level to 90 (SQL 2005)?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ron_Coffee" <Ron_Coffee@.discussions.microsoft.com> wrote in message
> news:181E0D5F-D699-477C-B266-31D2AE5B464E@.microsoft.com...
>
>

Deploy MSDE using Command

I am using VB6 to install MSDE and here is my syntax.
Setup.exe INSTANCENAME=NK SECURITYMODE=SQL SAPWD=NKM
It will install and show the progress bar all they way to about 4 second
left then freezes up. The log shows this as the last thing it did prior to
locking up "Executing "C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\sqlredis.exe /q:a"
Any idea what went wrong?
hi Grant,
Grant wrote:
> I am using VB6 to install MSDE and here is my syntax.
>
> Setup.exe INSTANCENAME=NK SECURITYMODE=SQL SAPWD=NKM
> It will install and show the progress bar all they way to about 4
> second left then freezes up. The log shows this as the last thing it
> did prior to locking up "Executing "C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\sqlredis.exe /q:a"
> Any idea what went wrong?
please have a look at http://tinyurl.com/dm5hs
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Interesting but I cannot do it through that process. It will be install on
thousands of customer so you can image the volume of calls we will get. I
will look for a different way to deploy MSDE. Thanks.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3vloskF164dfqU1@.individual.net...
> hi Grant,
> Grant wrote:
> please have a look at http://tinyurl.com/dm5hs
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>