CODEWITHSUNDEEP
sql-servert-sql
MeideC
MeideC

Reputation: 19

In SQL Server, get column names for a table WITHOUT using system tables

Some explanation on why this would be required and why information_schema.columns / syscolumns / spcolumns don't work in my situation:

I have this long procedure that maintains a dynamic table. The procedure saves the table into a temp table, creates an updated one using SELECT INTO and finally INSERTs some old rows from the temp table into the newly created table. This final part is done by looking up the temp table columns from a system table and generating dynamic INSERT INTO sql using a cursor looping through each column.

Now, these tables are often very large and the SELECT INTO takes a long time, which causes locks on some system tables. During this time tables such as information_schema.columns & syscolumns can't be read from. So when multiple tables are handled at the same time, they all stall until the locks are freed, because the procedure currently tries to read columns from the information_schema.columns table

Link where the systables lock issue is explored: https://www.sqlshack.com/sql-server-lock-issues-when-using-a-ddl-including-select-into-clause-in-long-running-transactions/

Any idea on how to approach this?

Upvotes: 1

Views: 418

Answers (1)

Sean Pearce
Sean Pearce

Reputation: 1169

Create the tables dynamically instead of select into.

For example: Instead of

SELECT name INTO #t FROM sys.objects;

Do

CREATE TABLE #t (name SYSNAME);
INSERT INTO #t SELECT name FROM sys.objects;

Upvotes: 1

Related Questions