//
you're reading...
SQL Server, Uncategorized

Get All Table,Field and properties in SQL Server

1. Getting All Table in database :

USE your_database
SELECT name FROM sys.tables

2. Getting All Field in database, if you want getting all field with description can use this :

SELECT u.name + '.' + t.name AS [table],
 td.value AS [table_desc],
 c.name AS [column],
 cd.value AS [column_desc]
FROM sysobjects t
INNER JOIN sysusers u
 ON u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
 ON td.major_id = t.id
 AND td.minor_id = 0
 AND td.name = 'MS_Description'
INNER JOIN syscolumns c
 ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
 ON cd.major_id = c.id
 AND cd.minor_id = c.colid
 AND cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY t.name, c.colorder

3. Getting Rows Count from all table in database :

SELECT 
 [TableName] = so.name, 
 [RowCount] = CAST(CONVERT(varchar, CAST(MAX(si.rows) AS Money), 1) AS varchar)
FROM 
 sysobjects so, 
 sysindexes si 
WHERE 
 so.xtype = 'U' 
 AND 
 si.id = OBJECT_ID(so.name) 
GROUP BY 
 so.name 
ORDER BY 
 2 DESC

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

September 2013
M T W T F S S
« Dec   Oct »
 1
2345678
9101112131415
16171819202122
23242526272829
30  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: