Script to check Link server name in View and Store Procedure

--Script to check Link server name in View / SP SELECT

Distinct

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 in ('YourLinkServerName') ---Script to check Link server in SQL Server 2005 SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules WHERE definition LIKE '%YourLinkServerName%'

How to Delete all rows from table

1. Disable all check constrains from table
2. Disable all triggers on all tables
3. Delete all data from tables
4. Enable all check constrains from table
5. Enable all triggers on all table

--Declare a global variable used throught the query

DECLARE @SQLStmt NVARCHAR(MAX) = N''




--Disable all check constraints on all tables

SELECT @SQLStmt = @SQLStmt + COALESCE('ALTER TABLE ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + ' NOCHECK CONSTRAINT ALL; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)

SET @SQLStmt = N''

--Disable all triggers on all tables




SELECT @SQLStmt = @SQLStmt + COALESCE(N'DISABLE TRIGGER ALL ON ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + '; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)

SET @SQLStmt = N''

--Delete all data from all tables




SELECT @SQLStmt = @SQLStmt + COALESCE('DELETE FROM ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + '; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)

SET @SQLStmt = N''

--Enable all constraints on all tables




SELECT @SQLStmt = @SQLStmt + COALESCE('ALTER TABLE ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + ' CHECK CONSTRAINT ALL; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)

SET @SQLStmt = N''

--Enable all triggers on all tables




SELECT @SQLStmt = @SQLStmt + COALESCE(N'ENABLE TRIGGER ALL ON ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + '; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)