I’m working on an “Intelligent” Universal Table Editor at the moment. The basic idea is we are sick to death of writing “CRUD” style search forms and editing forms when its just really sitting on top of a SQL table.
We have various frameworks in place to dynamically render editing and search forms from the base table plus some specific meta data. This allows support and customisation folk to add fields, search criteria, field level validation, and screen layout dynamically without the need for design time recompile and ship. Very useful for CRM style applications where things change quickly.
One challenge is how to get the Primary Keys of all the User Tables, along with enough meta data to be useful. Here is a sample query below:
-- Sample TSQL to retrieve primary keys on all user Tables select so.name as TableName, sc.name as PrimaryKeyName, st.name as PrimaryKeyType, sc.is_identity from sys.indexes si inner join sys.objects so on so.object_id = si.object_id inner join sys.sysindexkeys sik on sik.id = so.object_id and si.index_id=sik.indid inner join sys.columns sc on sc.object_id =so.object_id and sc.column_id=sik.colid inner join sys.types st on st.system_type_id=sc.system_type_id where si.is_primary_key =1 and so.type='U' and is_ms_shipped=0
