3

Using Delphi I need to create a class which will contain specific table structure (without data), including all fields, constraints, foreign keys, indexes. The goal is having "standard" table, compare them and find differences. This thing ought to be included into my big project so I can't use any "outer" comparators. Furthermore, this functionality might be extended, so I need to have my own realization. The question is how can I retrieve this information, having connection string and knowing specific table name. SQL Server 2008 is being used.

Yuriy
  • 257
  • 5
  • 12
  • 1
    While [the question](http://stackoverflow.com/questions/181909/tool-for-scripting-table-data) isn't the same, someone posted an answer (the one with the comment block and all the SQL script) that retrieves all the table definitions, database info, etc. that may help. – Ken White Feb 24 '12 at 13:52
  • There is no general way to do so, therefore you;ll have to declare an abstract base and populate actual schema data from DBMS-specific storage (eg: INFORMATION_SCHEMA on SQL-92 capable DBMS) – OnTheFly Feb 24 '12 at 17:28
  • Using TADOConnection.OpenSchema you will be able to get most of your information. other schema related information could be found in the sys tables (depending on your sql-sever version). – kobik Feb 25 '12 at 19:18

2 Answers2

3

If you look at Delphi source, it's done this way :

Select * from Table where 1=2

Update:

Metadata can be retrieved with Information Schema Views , for example constraints :

SELECT * FROM databaseName.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
Where TABLE_NAME='tableName'
Antonio Bakula
  • 20,445
  • 6
  • 75
  • 102
  • That only retrieves column names, not constraints, foreign keys, indexes, and other metadata. – Ken White Feb 24 '12 at 13:46
  • Using negative `where` condition waste lot of IO cycle as it need to evaluate the condition during SQL parsing and execution. For example, a firebird server consume `Execute time = 110ms` for negative `where` condition. Using `select first 0 * from table` consume `Execute time = 0ms` during execution. – Chau Chee Yang Jan 12 '17 at 03:13
0

Been a very long time since I touched Delphi, but I do remember a couple things I used to do. Like 

select top 0 * from table

returns 0 records but the TQuery is 'populated' with the meta data. Or I think on a TClientDataSet you can set the rows to -1, which has the same effect.

As I said, it's been a long time since I tinkered in Delphi and I was using the BDE and not native clients so this could all be useless info.

Hope this helps a bit though.

Dave Becker
  • 1,433
  • 1
  • 12
  • 24
  • Same as Antonio, hat only retrieves column names, not constraints, foreign keys, indexes, and other metadata. – Ken White Feb 24 '12 at 13:47
  • `select top` syntax isn't a standard SQL across different database server. Firebird server using `select first` and MySQL may use `limit` keyword. – Chau Chee Yang Jan 12 '17 at 03:15