CODEWITHSUNDEEP
sqlsql-server-2008
Taha Karaca
Taha Karaca

Reputation: 129

Getting the definition of the tables and columns via a SQL statement

I have a database and a lot of tables inside it. I wrote definition into the each table and column's definition part. And now using query I want to see all table and columns definition.

Can you please help me?

Upvotes: 4

Views: 30663

Answers (5)

HGMamaci
HGMamaci

Reputation: 1387

If you are looking for extended descriptions:

SELECT 
  OBJECT_NAME(major_id) TableName,(select name from sys.columns c where c.object_id=major_id and c.column_id=minor_id) ColumnName,*
FROM 
  sys.extended_properties
WHERE 
  name='MS_Description' AND minor_id>0 and class=1

Upvotes: 0

Durgesh Pandey
Durgesh Pandey

Reputation: 2422

try this to get the definition of table in SQL...

sp_help 'tableName'

example sp_help 'MyTable'

We can also do same thing as

exec sp_help 'MyTable'

Because sp_help is a pre define stored processor and we can execute stored processor using "exec" keyword or also can use "execute" keyword

Upvotes: 6

R S P
R S P

Reputation: 1077

Try this :

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

OR

for more detailed table information

EXEC sp_help [Your Table_Name]

Upvotes: 0

asantaballa
asantaballa

Reputation: 4048

Here's a bass query that can list info from the colums and tables in SQL server. Just add the columns you need from each system table.

SELECT 
  t.name table_name,
, c.name column_name
From sys.tables AS t
Inner Join sys.columns c 
  On t.Object_ID = c.Object_ID

Upvotes: 3

yswai1986
yswai1986

Reputation: 263

If you're using SqlServer.

SELECT obj.name, cols.name as columnname
from dbname.sys.objects as obj
inner join dbname.sys.columns as cols
on obj.object_id = cols.object_id
where obj.type='U'

Upvotes: 1

Related Questions