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?

Improve this question
4
  • 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) 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 CONSTRAINTs, TRIGGERS, KEYs, etc. Commented May 9, 2023 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 Commented May 9, 2023 at 13:05
  • True to a degree, yes, @Charlieface . Like I mentioned, they won't have access to things like the underlying objects (like CONSTRAINTSs), and if they are querying VIEWs then being able to SELECT from said VIEW doesn't allow you to see its definition. Commented May 9, 2023 at 13:13
  • Though 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). Commented May 9, 2023 at 13:18
Related questions
3327
How to add a column with a default value to an existing table in SQL Server?
2204
How to check if a column exists in a SQL Server table
1467
Check if table exists in SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.