Tag: SQL

• First, get all server name check if the server already linked or not

exec sp_linkedservers

• Link server who has windows authentication

exec sp_addlinkedserver    @server=’SERVERNAME’

• Link server who has login credentials

exec sp_addlinkedsrvlogin  ‘SERVERNAME’, ‘false’, null, ‘USERNAME’, ‘PASSWORD‘

DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR

SET @getDBName = CURSOR FOR

SELECT name
FROM sys.databases where name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’)

CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256))

OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = ‘USE ‘ + @DBName + ‘;
INSERT INTO #TmpTable
SELECT ”’+ @DBName + ”’ AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
FROM sys.tables’

EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END

CLOSE @getDBName
DEALLOCATE @getDBName
SELECT * FROM #TmpTable

Recover MS SQL statements which get deleted/closed accidentally

SELECT
deqs.last_execution_time AS [Time],
dest.TEXT AS [Query]
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID(‘YOURDATABASENAME’)
ORDER BY deqs.last_execution_time DESC

OR

SELECT
deqs.last_execution_time AS [Time],
dest.TEXT AS [Query]
FROM
sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY
deqs.last_execution_time DESC

Returns query with time.