As with most things in SQLite, there’s more than one way to get a list of temporary tables in a database.
Here I present two ways to return temporary tables in SQLite.
Continue readingTag: tables
2 Ways to List the Tables in an SQLite Database
Here are two ways to return a list of tables in all attached databases in SQLite.
The first method returns all tables and views for all attached databases.
The second method gives you the option of returning both tables and views, or just tables, but only for the primary database.
Update Dec 2021: Since writing this article, SQLite has introduced another option, which I’ve listed as a bonus third option at the end of this article.
Continue readingDifference Between Local and Global Temporary Tables in SQL Server
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.
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).