Why can't I find a table in a database?-CodePudding

USE mydb;

  create table #payments 
  (   id int,    contract_ID varchar(20),    payment_number int,    payment_status varchar(20));

  insert   #payments (id, contract_id, payment_number, payment_status)
  values
  (1, 'contract1', 1, 'Processed'),
  (2, 'contract1', 2, 'Received'),
  (3, 'contract2', 1, 'Processed'),
  (4, 'contract2', 2, 'Approved'),
  (5, 'contract1', 3, 'Approved'),
  (6, 'contract2', 3, 'Processed');

Is it correct that the above SQL script creates a table named #payments in the database 'mydb'?

When I run the above for the second time, SSMS says

There is already an object named '#payments' in the database.

But why can't I find the table in the output of

USE mydb;

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
  GROUP BY TABLE_NAME

Thanks

CodePudding user response：

Is it correct that the above SQL script creates a table named #payments in the database 'mydb'?

No, that's not correct.

# indicates a temporary table, it's created in tempdb

select Object_Id('tempdb.dbo.#payments');

select *
from tempdb.sys.tables
where name like '#payments%';

You'll see it exists with a negative object ID and name appended with a long "uniquifier".

Likewise prefixing any object name with # indicates it is a temporary object and will be managed for you in tempdb. Objects created with a single hash prefix are scoped to your own session, a double hash indicates they are global and visible to all sessions. Temporary objects are automatically dropped when they go out of scope, ie, are no longer referenced.

CodePudding user response：

Don't give hashtag (#) while writing table name.

Try below query it will create table and insert data.

USE mydb;

  create table payments (   id int,    contract_ID varchar(20),    payment_number int,    payment_status varchar(20));

  insert payments (id, contract_id, payment_number, payment_status) values (1, 'contract1', 1, 'Processed'),
  (2, 'contract1', 2, 'Received'),
  (3, 'contract2', 1, 'Processed'),
  (4, 'contract2', 2, 'Approved'),
  (5, 'contract1', 3, 'Approved'),
  (6, 'contract2', 3, 'Processed');

To find the table in the output

USE mydb;

SELECT * FROM mydb.payments;