generate table or database schema

generate table or database schema

• _jesse

  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).
  

• Denny Boynton

  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.
  
  

• Abe

  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
  ---

  
  

• KZoli

  can the schema be reverse engineered from visio for sql server 2005
  
  

• Alan Grosz

  Thanks, that was very helpful.
  

• R Raghu

  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

  
  
  

• The D

  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
  ---

  
  

• AL-Wakeel Adnan

  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
  ---
  
  
• generate table or database schema