geometric datatypes

Hi,
Is there any equivalent in SQL Server for Oracle's datatype 'SDO_GEOMETRY'
Smitha

No, there is no built-in data type for storing spatial data. In SQL Server 2005, you can create your own CLR user-defined type and use it. In addition, there are other ways to do deal with spatial data. See the links below for more details. They contain whitepaper, sample from SQL Server 2005 CD etc.

http://msdn.microsoft.com/sql/learning/prog/clr/default.aspx

http://msdn.microsoft.com/sql/learning/prog/clr/default.aspx?pull=/library/en-us/dnsql90/html/tblvalfuncsql.asp

http://msdn2.microsoft.com/en-us/library/ms345264(SQL.90).aspx

generating DDL

In Oracle, there is a system packaged function that returns the DDL for a
database object (DBMS_METADATA.GET_DDL). What is the equivalent in SQL
Server?you can generate DDL from an object using Query Analyzer by
right-clicking the object in the object browser and selecting "Script
Object to <dest> as <action>"
- Baileys
arch wrote:
> In Oracle, there is a system packaged function that returns the DDL for a
> database object (DBMS_METADATA.GET_DDL). What is the equivalent in SQL
> Server?
>|||Yes, I know. But I wanted the DDL to be used in a query. I need a function
or something that returns so I can use it in an SQL statement.
"Baileys" <no_email@.server.com> wrote in message
news:%23%23B7GGLhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> you can generate DDL from an object using Query Analyzer by right-clicking
> the object in the object browser and selecting "Script Object to <dest> as
> <action>"
> - Baileys
> arch wrote:|||You can use SQL DMO to get a table object and then use the script function t
o
script out the table.
You can search for the script function in Books Online. If you need this as
a function look for activating COM objects though the use of extended stored
procedures.
Arun
"arch" wrote:

> Yes, I know. But I wanted the DDL to be used in a query. I need a functi
on
> or something that returns so I can use it in an SQL statement.
> "Baileys" <no_email@.server.com> wrote in message
> news:%23%23B7GGLhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>
>|||arch wrote:
> Yes, I know. But I wanted the DDL to be used in a query. I need a functi
on
> or something that returns so I can use it in an SQL statement.
>
You can't directly generate SQL from within SQL code itself.
As another poster said, you can use DMO, which is a COM object (aka
ActiveX), with some surrounding code, to build your SQL statements,
then submit the to the database.
Or, in some cases you could roll your own sql-building code that would
work within SQL. If you're interested in tables, for example, the
metatable INFORMATION_SCHEMA.COLUMNS can give you column names, data
types and lengths; INFORMATION_SCHEME.CONSTRAINT_COLUMN_USAGE gives
information about which columns are involved in constraints, etc. That
would be a particularly tedious path to have to follow, in my opinion.

generate table or database schema

what is the sql servre equivalent to 'describe' keyword. how do we generate the schema of the database and tables of a database. schema here refers to column names and its types.

Hi,

have a look at the INFORMATION_SCHEMA views, in your case the

INFORMATION_SCHEMA.Columns

Select * from INFORMATION_SCHEMA.Columns

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||is there a way to just list the column name and column types of the tables in a database created by the user and not the system tables.|||

If you are using SQL Server 2005 the following query should work for you.

select name as ColumnName, TYPE_NAME(user_type_id) as TypeName, OBJECT_NAME(object_id) as ObjectName from sys.columns WHERE
OBJECTPROPERTY(object_id,'IsUserTable')=1

Let us know if this query is what you were looking for

|||Hi,

I am no freind of the sys tables, because they are supposed to (could) change between the versions of SQL Server, for the easiest way to get the information (also which helps you across sql server boundaries, because these are defined in the Ansi Spec), use the Information_Schema views and in additions the internal functions to get the *user*created tables:

SELECT * FROM
INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID(Table_Name),'IsUserTable')=1
HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||can the schema be reverse engineered from visio for sql server 2005?|||I'm just getting started with SQL Server, but does this mean that there is no simple equivalent to Oracle's "DESCRIBE" command? All I need to see is a table's basic schema (column datatypes and length, null allowed).|||Hi,

the describe of Oracle is just a implementation of Oracle in the PL/SQL language dialect. Its a special function or procedure (don′t know exactly how it is implemented in Oracle) which does simply a presentation of the metadata. For sql server there is a equivalent which displays a bit more than just the column definition, its sp_help <Objectname>. I just built a mimic procedure for you to display the metadata you wanted:

CREATE PROCEDURE DESCRIBE

(

@.TableName VARCHAR(100)

)

AS

BEGIN

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @.TableName And TABLE_TYPE = 'BASE TABLE')

PRINT 'No such table present in current database.'

ELSE

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @.TableName

END

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks, that was very helpful.