[SQL Server] Search Text in Database Objects

We can easily search the data that we store in our database tables through queries, but what if we want to search the database objects, like stored procedure, trigger, views, user-defined functions, etc. For instance, if we want to know if any specific table is used in any database object, or want to search any special comment mentioned in any of these DB objects. Here is what we do to get it done:

SELECT O.[name] AS [ObjectName]
, O.[type_desc] AS [ObjectType]
, O.[modify_date] AS [ModifiedDate]
, C.text
FROM sys.syscomments C
INNER JOIN sys.objects O ON C.[id] = O.[object_id]
WHERE C.[text] LIKE '%<text-to-search>%'
AND O.[type] in ('V', 'FN', 'TR', 'P')

Solution is just like Aladin's Lamp...! Enjoy.

[T-SQL] Alter Table - Add/Drop Constraint Query

It is a good practice to identify and apply constraints while creating the table, but in real scenario, we usually require to add constraint on existing column or while adding a new column in existing table. For this purpose, a simple and quick solution is to do it through query. Below is how you achieve it:

Add Constraint on Existing Column:

ALTER TABLE <table-name>
ADD CONSTRAINT <constraint-name> 
PRIMARY KEY (<column-name>)

where, "<constraint-name>" is a user defined constraint name to refer it properly.

The above syntax could be used for UNIQUE, DEFAULT and CHECK constraints, but for FOREIGN KEY constraint, the query would be as follows:

ALTER TABLE <target-table-name>
ADD CONSTRAINT <constraint-name>
FOREIGN KEY ( <target-column-name> )
REFERENCES <source-table-name> ( <source-column-name> )

Add Constraint on New Column:

ALTER TABLE <table-name>
ADD <column-name> BIGINT IDENTITY
CONSTRAINT <constraint-name> PRIMARY KEY

where, "<constraint-name>" is a user defined constraint name and "IDENTITY"  is optional if you want to make this identity with default seed and increment value set to 1.


The above syntax could be used for UNIQUE, DEFAULT and CHECK constraints, but for FOREIGN KEY constraint, the query would be as follows:

ALTER TABLE <target-table-name>
ADD <target-column-name> BIGINT
CONSTRAINT <constraint-name>
FOREIGN KEY ( <target-column-name> )
REFERENCES <source-table-name> ( <source-column-name> )

Drop Constraint from Existing Column:

To drop a constraint is very simple:

ALTER TABLE <table-name> 
DROP CONSTRAINT <constraint-name>

Enjoy!