1 Replies - 468 Views - Last Post: 24 April 2014 - 12:29 PM Rate Topic: -----

#1 Duckington  Icon User is offline

  • D.I.C Addict

Reputation: 170
  • View blog
  • Posts: 608
  • Joined: 12-October 09

How to get table information in SQL Server and PostgreSQL?

Posted 24 April 2014 - 01:32 AM

I have this query that is used if the database behind our system is MySQL, which gets a list of tables with a certain prefix, the number of rows, the size and whether or not they are indexed:

$query = $this->query("SELECT table_name as name, (auto_increment - 1) as records, index_length as indexsize, table_comment as cmt, ( data_length + index_length ) as size
                                   FROM information_schema.tables
                                   WHERE table_schema = ? AND table_name LIKE ?
                                   ORDER BY table_name", array($this->conn->db, '%'.$tablePrefix.'%'));



(I might change the number of records bit, not sure yet)

I've been trying to work out how I can duplicate this to work on SQL Server and PostgreSQL databases as well, but I have very little experience with them.

Could anyone point me in the right direction for this?

Cheers.

This post has been edited by Duckington: 24 April 2014 - 01:32 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How to get table information in SQL Server and PostgreSQL?

#2 smendoza88  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 61
  • Joined: 13-July 11

Re: How to get table information in SQL Server and PostgreSQL?

Posted 24 April 2014 - 12:29 PM

For MSSQL all the info would be in the sys.tables, sys.columns, sys.indexes, sys.constraints you would have to play around with them to see what data you need

Here I pulled back all the info for sys.tables and sys.columns. This was tested in mssql 2008 r2
select * 
from sys.tables as t inner join sys.columns as c 
  on t.object_id  = c.object_id
where t.name = 'pm_city'


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1