This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Sunday, September 15, 2013

ALTER computed column

There is no special command in SQL Server to ALTER any computed column.
But we can do by using below two ways:

1. Drop computed column and recreate
2. GO To Table in Object Explorer , right click on table and open in DESIGN mode.
Identify the column and edit "COMPUTED COLUMN Specification > Formula " property .

Second method will loose all existing value in computed column so best is to go for first method, it will drop, recreate and populate column again.
But never forget to note computed column expression before dropping column.


Tuesday, September 3, 2013

List All Objects Using Linked Server

While deployment a project on Production server I got an requirement to update my all LINKED Server references which is a very boring  task to me.
I found a very useful function on MSDN to check cross-database dependencies.

/*-----List objects using Linked Server-------*/
    referenced_Server_name As LinkedServerName,
    referenced_schema_name AS LinkedServerSchema,
    referenced_database_name AS LinkedServerDB,
    referenced_entity_name As LinkedServerTable,
    OBJECT_NAME (referencing_id) AS ObjectUsingLinkedServer
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
And referenced_Server_name = 'Enter LinkedServerName here'