how to get table list and there structure in sql server

Question

how can i get table list and its structure from sql server.. i use the following code to get the database list from sql server.

SELECT * FROM sys.databases WHERE sys.databases.database_id > 4

this query return's database "name" and "id" and many other fied...

and i my .net app show bind the list with dropdown... now i need to get the table list of selected database in the dropdown on the basis of it's id...

what is the query to get the table list of selected database.

score 0 · Answer 1 · answered Sep 24 '13 at 17:41

0

select name from sys.tables where type = 'U'  --U = user defined tables as opposed to system tables

answered Sep 24 '13 at 17:41

JohnLBevan

22,735
13
96
178

i need to get the table list on basis of database id. – Abdul Basit Sep 24 '13 at 17:43
1

The list will be given for the database you're in - so you'd need to connect to that database/catalogue first. You could do something like this if you wanted all dbs' tables: `sp_msforeachdb 'select ''?'' dbName, db.database_id, t.* from sys.databases db cross join [?].sys.tables t where db.name=''?'''` – JohnLBevan Sep 24 '13 at 17:45

score 0 · Answer 2 · answered Sep 25 '13 at 05:22

Try this one -

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'SELECT ''' + d.name + ''', name, object_id FROM [' + d.name + '].sys.objects WHERE type = ''U'''
    FROM sys.databases d
    WHERE d.state_desc != 'OFFLINE'
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')

PRINT @SQL
EXEC sys.sp_executesql @SQL

Output -

db     name                                   object_id
------ -------------------------------------- -----------
master spt_monitor                            68195293
master spt_fallback_db                        117575457
master spt_fallback_dev                       133575514
master spt_fallback_usg                       149575571
master MSreplication_options                  1787153412

db   name                                   object_id
---- -------------------------------------- -----------
msdb sysmail_profile                        14623095
msdb syscollector_config_store_internal     23671132
msdb DTA_reports_queryindex                 38291196

how to get table list and there structure in sql server

2 Answers2