how to get table name with its columns and their types

25 Aug 2005 3:00 PM

Hasan O

Hi ,
Let me explain the scenio . I have the following table :

CREATE TABLE [dbo].[User] (
[userid] [int] IDENTITY (1, 1) NOT NULL ,
[username] [varchar] (50) NOT NULL ,
[password] [varchar] (255) NOT NULL
) ON [PRIMARY]
GO

I want to write a query that returns all the tablenames with their columns
and their type from the selected database

use DataBaseName
select tablename , columnname , columntype from .........

its output would be like this

tablename      columnname      columntype
-------------------------------------------
User                userid                int
User                username          varchar[50]
User                password          varchar[255]

Any suggestions?
Thanks for the incoming answers.

25 Aug 2005 3:04 PM

Aaron Bertrand [SQL Server MVP]

http://www.aspfaq.com/search.asp?q=schema%3A

http://www.aspfaq.com/2177

Show quote

"Hasan O" <hozaval***@gmail.com> wrote in message
news:O4Ov9WYqFHA.2076@TK2MSFTNGP14.phx.gbl...
> Hi ,
> Let me explain the scenio . I have the following table :
>
> CREATE TABLE [dbo].[User] (
> [userid] [int] IDENTITY (1, 1) NOT NULL ,
> [username] [varchar] (50) NOT NULL ,
> [password] [varchar] (255) NOT NULL
> ) ON [PRIMARY]
> GO
>
> I want to write a query that returns all the tablenames with their
> columns and their type from the selected database
>
> use DataBaseName
> select tablename , columnname , columntype from .........
>
> its output would be like this
>
> tablename      columnname      columntype
> -------------------------------------------
> User                userid                int
> User                username          varchar[50]
> User                password          varchar[255]
>
>
> Any suggestions?
> Thanks for the incoming answers.
>

26 Aug 2005 3:12 PM

Marcel

Try this:

select *
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'mytable'

Marcel van Eijkel
( www.vaneijkel.com )