Sometimes questions in the interview are extremely open ended and such questions can be very useful for candidate to play around with. For example, in one of the recent interview one of the interviewer asked the candidate that what is his favorite T-SQL script as a database administrator. The answer of the candidate was very simple, however, he was able to earn some brownie points with the help of his script.
Question: How do you retrieve user defined object details?
Answer: Here is a simple script which can help retrieve user defined object’s details with the help of sys.objects.
The sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database.
Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of the foreign key, name of the table it FK belongs and the schema owner name of the table.
USE AdventureWorks;
GO
SELECT name AS ObjectName,
OBJECT_NAME(schema_id) SchemaName,
OBJECT_NAME(parent_object_id) ParentObjectName, name, *
FROM sys.objects
WHERE TYPE = 'F'
GO
You can use any of the following in your WHERE clause and retrieve necessary information.
Object type:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table
Reference: Pinal Dave (https://blog.sqlauthority.com)
