Copy Table DDL Between Servers

This script will allow you to specify a linked server name, a remote database on that linked server, and a local database name in which to copy all the tables from on server onto another. CREATE PROCEDURE [dba].[pr_refresh_create_tables]     @linked_server VARCHAR(100),     @source_db VARCHAR(100),     @target_db VARCHAR(100),     @debug_mode INT = 0 AS — dba.pr_refresh_create_tables ‘’, ‘yourdbname’, ‘targetdb’, 0 — SELECT * FROM admindb.dbo.refresh_table_columns BEGIN     DECLARE @SQL nvarchar(MAX);     DECLARE @SQLColumnList nvarchar(MAX);     DECLARE @SQLSelectColumnList nvarchar(MAX);     DECLARE @CRLF nvarchar(2);     DECLARE @Tab nvarchar(1);     DECLARE @RecordKeyName nvarchar(50);     DECLARE @RecordKeyDataType nvarchar(50);     DECLARE @TableID INT;     DECLARE @TableCount INT;     DECLARE @SOURCE VARCHAR(MAX) = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’     DECLARE @TableName VARCHAR(255);     DECLARE @TableList TABLE (         TableID INT IDENTITY(1, 1) NOT NULL,         TableName VARCHAR(255)         );     IF OBJECT_ID(N’admindb.dbo.refresh_table_columns_tmp’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns_tmp     IF OBJECT_ID(N’admindb.dbo.refresh_table_columns’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns     IF OBJECT_ID(N’admindb.tmp.refresh_create_tables’) IS NOT NULL DROP TABLE admindb.tmp.refresh_create_tables     CREATE TABLE admindb.dbo.refresh_table_columns_tmp (         RowID INT NOT NULL,         TableName VARCHAR(255),         ColumnName VARCHAR(255),         IsIdentity bit,         DataType VARCHAR(50),         IsDataTypeCast bit,         IsNullable bit,         IsLast bit       […]

Continue reading ...

Toggle Jobs On and Off

This is a useful script if you want to turn a specific list of jobs that are currently enable to a disabled status and vice versa. Just specify the list of job names in the VALUES section below: USE msdb GO IF OBJECT_ID(N’tempdb..#jobs’) IS NOT NULL BEGIN     DROP TABLE #jobs END CREATE TABLE #jobs (     job_name nvarchar(MAX), ) INSERT INTO #jobs(job_name) VALUES     (’Job Name 1 here’),     (’Job Name 2 here’),     (’Run index selection Job’) IF OBJECT_ID(N’tempdb..#job_status’) IS NOT NULL BEGIN     DROP TABLE #job_status END SELECT ID = IDENTITY(INT, 1,1),     [job_name], enabled INTO #job_status FROM [msdb].[dbo].[sysjobs] AS [sJOB] JOIN #jobs j     ON j.job_name = DECLARE @job_name nvarchar(MAX) = ”; DECLARE @enabled INT; DECLARE @iter INT = (SELECT MAX(ID) FROM #job_status); WHILE @iter > 0 BEGIN     SET @job_name = ”     SELECT @job_name = job_name,         @enabled = CASE WHEN [enabled] = 0 THEN 1 ELSE 0 END     FROM #job_status     WHERE ID = @iter;     EXEC msdb.dbo.sp_update_job @job_name = @job_name,    @enabled = @enabled ;     PRINT CAST(@job_name AS VARCHAR) + ‘:’ + CAST(@enabled AS VARCHAR)     SET @iter -= 1; END

Continue reading ...

Find CPU from within SSMS

This is a great DMV if you don’t want to leave SSMS to see the CPU usage or if you don’t have access. What’s good about this method is it also shows you the history of the cpu usage. DECLARE @ts_now BIGINT SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info; SELECT record_id, dateadd(ms, -1 * (@ts_now – [TIMESTAMP]), GetDate()) AS EventTime, SQLProcessUtilization, SystemIdle, 100 – SystemIdle – SQLProcessUtilization AS OtherProcessUtilization FROM ( SELECT record.VALUE('(.[email protected])[1]’, ‘int’) AS record_id, record.VALUE('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) AS SystemIdle, record.VALUE('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) AS SQLProcessUtilization, TIMESTAMP FROM ( SELECT TIMESTAMP, CONVERT(xml, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’ AND record LIKE ‘%<SystemHealth>%’) AS x ) AS y ORDER BY record_id DESC

Continue reading ...

Increase Job History

The SQL Server Agent by default only keeps 1000 rows of history for all jobs. There is also a limitation for each job to keep only 100 rows each. These rows do not only include the a row for the entire job, but the rows for each step in the job also. These rows can quickly get filled leaving you in the dark when you need to troubleshoot what had happened in a job. To change these values, you need to have sysadmin access to the SQL Server Agent. Within the object explorer, right click on the SQL Server Agent node, and choose properties. A new pop-up window will appear displaying the SQL Server Agent properties. Select the History node and the history settings are displayed: Here you have the option to not limit the agent job history, by unchecking the corresponding box. You can also choose the maximum # of rows to store for all jobs, and an option to limit each job. The last option is to Remove history that’s older than a specified time period. This would be recommended if you do not limit the history log size, since you will not be pruning any records otherwise.

Continue reading ...


This command shows the last statement executed for a particular SPID. This statement is mostly used for troubleshooting purposes to determine the exact command a particular SPID is running. You must be a member of the sys admin fixed server role, or have VIEW SERVER STATE permission (if SQL 2005+). As for a real world use, I will mostly use this command after using sp_who2 to find out which SPID is taking a lot of resources. To execute, simply replace the SPID 55 below with the one you want to spy on. DBCC INPUTBUFFER(55) And here are the results: The results you are looking for are displayed in the third column above. This shows the last statement that has been or is currently being executed by the SPID. Another alternative to the DBCC INPUTBUFFER is to use the dm_exec_requests DMV. This query will show the currently executing statement for a SPID: SELECT     SQLStatement       =         SUBSTRING         (             qt.text,             er.statement_start_offset/2,             (CASE WHEN er.statement_end_offset = -1                 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2                 ELSE er.statement_end_offset                 END – er.statement_start_offset)/2         )         FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt WHERE er.session_id = 54 This […]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!