0

I need to recreate the table structure of a SQL server database, but I only have SELECT-access through a textbox in some esoteric software running on a locked-down VM somewhere far away. Through this textbox I can query pretty much anything, but I don't have something like a connection string or command line.

What would be the easiest way to recreate the table structure on a local database?

My guess would be to use system catalog views like sys.all_objects and sys.all_columns, but I don't see an easy way to convert this data to CREATE TABLE-statements. Is there a straightforward way to accomplish this, or am I condemned to writing an error-prone 'conversion script' myself?

superbadcodemonkey
  • 810
  • 4
  • 23
  • 2
    If you have `SELECT` only access you likely don't have access to be able to view the definition of the objects. As such you might have to use [sys.dm_exec_describe_first_result_set (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql) and pass it a `SELECT *` statement against the table; that'll give you the column and their data types which you'll be able to build your own DDL statement from. Of course, you won't have any details of `CONSTRAINT`s, `TRIGGERS`, `KEY`s, etc. – Thom A May 09 '23 at 12:58
  • @ThomA *"The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission."* so it would be visible – Charlieface May 09 '23 at 13:05
  • True to a degree, yes, @Charlieface . Like I mentioned, they won't have access to things like the underlying objects (like `CONSTRAINTS`s), and if they are querying `VIEW`s then being able to `SELECT` from said `VIEW` doesn't allow you to see its definition. – Thom A May 09 '23 at 13:13
  • Though [sys.dm\_exec\_describe\_first\_result\_set (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql) does at least give the data types in a more "consumable" format, with values like `varchar(10)` and `decimal(10,2)`. – Thom A May 09 '23 at 13:18

0 Answers0