CODEWITHSUNDEEP
sql-server
Malfist
Malfist

Reputation: 31815

Printing table's structure/schema

Tables in database RentalEase I want to print physically so I can look at them easier. I don't care what is in them, just their structure. How can I do this?

It's an SQL Express server and I'm using Microsoft SQL Server Management Studio Express to manage it. In MySQL and PHP I use DESCRIBE but there doesn't seem to be a DESCRIBE for SQL Server

Upvotes: 24

Views: 112037

Answers (6)

Mohammad Ansari
Mohammad Ansari

Reputation: 1549

you need sp_help stored procedure like this :

exec sp_help  'tableName'

be careful to put your table name between ' '

Upvotes: 0

Ash
Ash

Reputation: 62165

In Management Studio,

  1. Click the "+" next to your database expanding the objects below it and click on "Tables"
  2. Open the Tables detail view by selecting "View" -> "Object Explorer Details" from the menu
  3. Now select all tables (on the right hand side in the object details)
  4. right click on any of the selected Tables (on the right hand side)
  5. "Script table As" -> "Create To"
  6. "File" or "Clipboard"

This will produce a script file containing all of the selected file schema definitions.

Upvotes: 9

Brian H
Brian H

Reputation: 85

Here is a script I wrote that simply lists each table and their columns in a format like:

Table Columns

table1 column1,column2...columnX

The script is:

declare @max_tables int
declare @max_columns int
declare @sql nvarchar(400)
declare @x int
declare @y int
declare @table varchar(50)
declare @columns varchar(800)

create table #c ([Table] varchar(50),[Columns] varchar(800))

select ROW_NUMBER() OVER(ORDER BY name) AS Row, name 
into #table_list
from sys.objects 
where type_desc = 'USER_TABLE' 
order by name

set @max_tables = (select count(*) from sys.objects where type_desc = 'USER_TABLE')
set @y = 0

while @y < @max_tables
    begin
        set @y = @y + 1
        set @table = (select name from #table_list where row = @y)

        create table #t (c int)

        set @sql = 'select count(*) as c from Information_schema.Columns where table_name = ''' + @table + ''''
        insert into #t exec sp_executesql @sql

        set @max_columns = (select top 1 c from #t)

        DROP TABLE #t

        set @x = 0
        set @columns = ''

        while @x < @max_columns 
            begin
                set @x = @x + 1
                set @columns = @columns + (select column_name from Information_schema.Columns where table_name = @table and ordinal_position = @x)
                if @x < @max_columns set @columns = @columns + ', '
            end

        insert into #c select @table,@columns

    end

select * from #c

DROP TABLE #c
DROP TABLE #table_List

Upvotes: 3

Robin
Robin

Reputation: 2616

try: 

sp_help <table_name>

Upvotes: 38

marc_s
marc_s

Reputation: 755531

You can always inspect the INFORMATION_SCHEMA views to find all the interesting information about tables and their columns.

It's not called "describe" per se - but this query will show you lots of information:

select * from Information_schema.Columns
where table_name = '(your table here)'

Marc

Upvotes: 15

Alex Reitbort
Alex Reitbort

Reputation: 13706

You can use Database Schema Diagram Design Tool. Just drop all the tables there, and you will get the diagram of you database including all keys

Upvotes: 3

Related Questions