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.