User Defined Functions

As part of SQL Server, there are two types of functions.

1) Built-In/System Function

2) User defined functions

In this blog we discuss about the User Defined Functions:

User defined Function,is like a TSQL program/Procedure that accepts the parameters and provides shortcut results through programming shortcuts

TSQL – Find Object Dependencies

Yeah!!.. it is getting pretty much interesting here.. the following query we can use to find the Tables used in the stored procedures (‘Object on which the Stored Procedure depends’).  It is also can be viewed by right click the object and click the ‘View Dependency’ , it will give results.. but the TSQL as follows:
SELECT b.name,a.referencing_id, a.referenced_database_name, a.referenced_entity_name
FROM sys.sql_expression_dependencies a
JOIN sys.procedures b on a.referencing_id = b.object_id

But…regardless of whether it is stored procedure or View,  if we want to find the dependency of all the object in the database use the following script.

SELECT b.name,referencing_id,referenced_database_name, referenced_entity_name
FROM sys.sql_expression_dependencies a
JOIN sys.objects b on a.referencing_id = b.object_id

…Hope It Saves time.  :)

TSQL – Drop/ADD FK constraints in database

There will be some scenario, where we might need to drop the FK constraints and recreate them again while doing truncate in the tables where referential integrity is created between tables. Here is the script that create the the Constraints scripts from the existing relationship between the tables.
Following Query will create the “Alter table ADD Foreign Key constraint” script.
USE [AdventureWorks]
SELECT 'Alter Table  ['+ d.name+'].['+OBJECT_NAME(a.parent_object_id) + ']   With Check Add Constraint ['+ a.name +']  Foreign Key (['+ COL_NAME(b.parent_object_id,b.parent_column_id)+']) References ['+d.name+'].['+OBJECT_NAME (a.referenced_object_id)+'] (['+COL_NAME(b.referenced_object_id,b.referenced_column_id) +'])'
FROM sys.foreign_keys AS a
JOIN sys.foreign_key_columns AS b ON a.OBJECT_ID = b.constraint_object_id
JOIN sys.objects  c on a.name = c.name and c.type = 'F'
JOIN sys.schemas d ON d.schema_id = c.schema_id
Following query will create the “Alter table DROP Foreign Key constraints”  script.
USE [AdventureWorks]
SELECT 'Alter Table  ['+c.name+'].[' + b.name   +']  Drop Constraint [' + a.name +']'
FROM  sys.objects a
JOIN     sys.objects b on a.parent_object_id = b.object_id
JOIN     sys.schemas c on a.schema_id = c.schema_id
WHERE a.parent_object_id IN  (SELECT object_id FROM sys.objects ) AND a.type in ('F ')  
Note: I want you to take  “Alter table ADD Foreign Key constraint” script backup before running the “Alter table DROP Foreign Key constraints”  scripts on the table. Also you can play with the script as you needed. Hope it helps and saves time.