When you create a temporary table in SQL Server, you have the option of making it a local or global temporary table.
Here’s a quick outline of the main differences between local temporary tables and global temporary tables.
Tag: tables
Create a Temporary Table Based on Another Table in SQL Server
In SQL Server you can create a temporary table based on another table by using the SELECT... INTO syntax.
You can create the table with or without data. In other words, you can copy data from the original table if you wish, or you can create the table without any data.
Create a Temporary Table in SQL Server
In SQL Server, temporary tables are created using the same CREATE TABLE syntax as regular tables. The difference is that temporary tables’ names are prefixed with either one or two number signs (#), depending on whether it’s a local temporary table or global temporary table:
- Local temporary tables are prefixed with a single number sign (
#) - Global temporary tables are prefixed with a double number sign (
##)
Introduction to Temporary Tables in SQL Server
In SQL Server, a temporary table is a certain kind of table that exists until goes out of scope (unless it’s explicitly dropped).
This is different to a regular (persistent) table, where the regular table exists permanently in your database until you explicitly drop it.
Check if a Table is Referenced by a Foreign Key in SQL Server with OBJECTPROPERTY()
You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table is referenced by a foreign key.
To do this, pass the table’s object ID as the first argument, and TableHasForeignRef as the second argument. The function returns a 1 or a 0 depending on whether or not it is referenced by a foreign key.
A return value of 1 means that the table is referenced by a foreign key, and a value of 0 means that it’s not.
Note that the examples presented here don’t list out the foreign keys or their tables or anything like that. They simply return a true/false value that you can use to test whether or not a table is referenced by a foreign key. If you need to list out all foreign keys that reference a given table, see Return All Foreign Keys that Reference a Given Table in SQL Server. The examples in that article list out each foreign key, as well as the foreign key table/s, and the primary key table.
See if a Table has a DEFAULT Constraint in SQL Server using OBJECTPROPERTY()
You can use the OBJECTPROPERTY() function in SQL Server to see whether or not a table has a DEFAULT constraint.
To do this, pass the table’s object ID as the first argument, and TableHasDefaultCnst as the second argument. The function returns a 1 or a 0 depending on whether or not it has a DEFAULT constraint.
A return value of 1 means that the table has a DEFAULT constraint, and a value of 0 means that it’s not.
Find Out if an Object is a User-Defined Table in SQL Server with OBJECTPROPERTY()
You can use the OBJECTPROPERTY() function in SQL Server to check whether an object is a user-defined table or not.
To do this, pass the object ID as the first argument, and IsUserTable as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a user-defined table.
A return value of 1 means that it is a user-defined table, and a value of 0 means that it’s not.
Use OBJECTPROPERTY() to Find Out if a Table is a System Table in SQL Server
The OBJECTPROPERTY() function in SQL Server enables you to check an object for a specific property.
You can use this function to check whether an object is a system table or not. To do this, pass the object ID as the first argument, and IsSystemTable as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a system table (1 means that it is a system table, and 0 means that it’s not).
5 Ways to Count the Number of User-Defined Tables in a SQL Server Database
Below are five methods you can use to quickly determine how many user-defined tables are in the current database in SQL Server.
All five options use the COUNT() function to get the count. Obviously, you can replace this with an asterisk (*), or the names of the columns to return a list of all user-defined tables.
3 Ways to Count the Number of System Tables in a SQL Server Database
Here’s an article that presents three ways to quickly determine how many system tables are in the current database in SQL Server.
All three options use the COUNT() function while querying the sys.objects system catalog view. They all result in the same output, so you really don’t need to go past the first option. But I’ll list them anyway.