There are some faster ways to generate a tally table. The code you are looking at is selecting data from actual tables and inserting into a temp table. You can instead use some trickery to avoid ANY physical reads.

This code will produce 10,000 records nearly instantly.

WITH

E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

select N from cteTally

I have a couple versions of this that I keep around in different formats. One is a view and the other one is an iTVF. They both get used pretty frequently.

Here is code to turn this into a view.

create View [dbo].[cteTally] as

WITH

E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

select N from cteTally

GO

OK so this only generates 10,000 rows and yours generates 11,000. No problem, we can still do this amazingly fast.

select ROW_NUMBER() over(order by (select null))

from

(

select * from dbo.cteTally c

union all

select top 1000 * from dbo.cteTally t

) x

This type of thing will help your system because it requires far less resources than pulling then sys.columns and inserting into a temp table. No need to check for the existence or drop any objects.