Tag: mssql

Check if an Object is a Stored Procedure by Using OBJECTPROPERTY() in SQL Server

Posted on by Ian

In SQL Server you can use the OBJECTPROPERTY() function to find out whether or not an object is a stored procedure.

This function accepts two parameters: the object ID, and the property for which you’re checking it for.

Therefore, you can pass the object ID as the first argument, and IsProcedure as the second, and the function will return either a 1 or a 0 depending on whether or not it’s a stored procedure.

A return value of 1 means that it is a stored procedure, and a value of 0 means that it’s not.

Continue reading →

Use OBJECTPROPERTY() to Find Out if an Object is a CHECK Constraint in SQL Server

Posted on by Ian

In SQL Server you can use the OBJECTPROPERTY() function to find out whether or not an object is a CHECK constraint.

This function accepts two parameters: the object ID, and the property for which you’re checking it for.

Therefore, you can pass the object ID as the first argument, and IsCheckCnst as the second, and the function will return either a 1 or a 0 depending on whether or not it’s a CHECK constraint.

A return value of 1 means that it is a CHECK constraint, and a value of 0 means that it’s not.

Continue reading →

3 Ways to Count the Number of System Tables in a SQL Server Database

Posted on by Ian

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.

Continue reading →

Check if an Object is a Table, View, or Stored Procedure in SQL Server using the OBJECTPROPERTY() Function

Posted on by Ian

In SQL Server you can use the OBJECTPROPERTY() function to check an object’s type. More specifically, you can check whether it is or isn’t a specific type.

For example, the IsTable property tells you whether or not it’s a table, the IsView property tells you whether or not it’s a view, etc.

This article offers a few basic examples that check whether an object is a table, view, stored procedure, or table-valued function.

Continue reading →

3 Ways to Return All Tables WITHOUT a Primary Key in SQL Server

Posted on by Ian

If you ever need to find out whether a database has any tables that don’t have a primary key, you can run any of the following scripts in SQL Server to return just those tables.

All of these scripts take advantage of the OBJECTPROPERTY() function. This function accepts a TableHasPrimaryKey argument that you can check for a value of 0. If it’s 0, the table doesn’t have a primary key. If it’s 1 it does. Therefore, you can also use this function to return all tables with a primary key.

These scripts simply return the name of the table and its schema, but you can always modify them to return more columns.

Continue reading →

7 Ways to Return All Tables with a Primary Key in SQL Server

Posted on by Ian

If you ever find yourself needing to find all tables that have a primary key in SQL Server, this article can help.

This article offers seven ways to return all tables in the current database that have a primary key.

Note that most of these examples return only the tables – not the primary keys themselves. If you want a list of primary keys, see 11 Ways to Return a Primary Key in SQL Server.

Continue reading →

7 Ways to Return All Tables with Foreign Keys in SQL Server

Posted on by Ian

This article offers seven ways to return all tables that have foreign keys in a database in SQL Server.

Each table is returned just once, regardless of how many foreign keys it may have. This is different to returning all foreign keys, along with their tables. If you want to do that, see 11 Ways to Return Foreign Keys in SQL Server.

All examples here query the same database, and therefore return the same result.

Continue reading →

Use APP_NAME() to Get the Application Name of the Current Session in SQL Server

Posted on by Ian

In SQL Server, you can use the APP_NAME() function to get the application name for the current session. This assumes the application sets that name value.

You can use this function to distinguish between different applications, as a way to perform different actions for those applications.

Note that the client provides the application name, and so the result returned by this function simply reflects whatever name the client provides. For this reason, Microsoft advises that this function should not be used for security checks.

Continue reading →