I need to create an implicit select statement for each table with all of its coluns – i.e. SELECT COL1, COL2, COL3 FROM TABLE1 instead of SELECT * FROM TABLE1.
There are too many tables to use the scripting feature in SMSS so I was hoping to use one of the system tables. I can query the table and column names from INFORMATION_SCHEMA.COLUMNS, but the I'd have to use a cursor to build the string as required.
Just hoping there's an easier way of doing it.
Thanks
Best Answer
Not sure that this is simpler than a cursor solution, but here is what I would do:
Before we begin we need a few tables in a SQL Fiddle:
MS SQL Server 2012 Schema Setup
First we need the list of all user tables. For that we can use the
sys.tables
catalog view. TheOBJECT_SCHEMA_NAME()
function gets us the schema name, theOBJECT_NAME()
function the table name. TheQUOTENAME()
function quotes the names correctly, in case some of those contain special characters, key words or spaces. (In this example the use ofOBJECT_NAME()
is not strictly necessary assys.tables
has a name column, but I left it in as you can use this pattern with any catalog view that has anobject_id
column.)Query 1:
Results:
The next step is to get the list of column names, again quoted. We can use the
sys.columns
catalog view for that.Query 2:
Results:
The next hurdle is to get those columns in a comma separated list. There is no string concatenation aggregate function build in so we have to use a trick:
Query 3:
Results:
With that all pieces are in place and we just have to put them all together:
Query 4:
Results:
This example will work in SQL 2005 and later, assuming you are on the latest service pack. There is no clean solution to achieve this with SQL Server 2000. In that case you need to go back to your cursor solution.