Copy Stored Procedures Between Servers

This procedure will migrate all your stored procedures located on on server to another via linked server. Just specify the linked server name, remote database, and local database. [cc lang=”sql”] CREATE PROCEDURE [dba].[pr_refresh_create_procedures] @linked_server varchar(100), @source_db varchar(100), @target_db varchar(100) AS –EXEC [dba].[pr_refresh_create_procedures] ‘LOCALSERVER’, ‘yourdbname’, ‘targetdb_dev’ SET @source_db = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’; DECLARE @sql Nvarchar(max) DECLARE @Name varchar(255) = @target_db IF OBJECT_ID(N’admindb.tmp.shelldb_copy_procedures’) IS NOT NULL DROP TABLE admindb.tmp.shelldb_copy_procedures SET @sql = ‘SELECT p.name, m.Definition INTO admindb.tmp.shelldb_copy_procedures FROM ‘ + @source_db + ‘.sys.objects p WITH (NOLOCK) INNER JOIN ‘ + @source_db + ‘.sys.sql_modules m WITH (NOLOCK) ON p.object_id = m.object_id WHERE type IN (”FN”, ”IF”, ”TF”, ”P”, ”V”, ”TT”)’ exec(@sql) DECLARE c CURSOR FOR SELECT Definition FROM admindb.tmp.shelldb_copy_procedures OPEN c FETCH NEXT FROM c INTO @sql WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = REPLACE(@sql,””,”””) set @sql = N’execute ‘ + QUOTENAME(@name) + N’.dbo.sp_executesql N”’ + @sql + ”” EXEC(@sql) FETCH NEXT FROM c INTO @sql END CLOSE c DEALLOCATE c [/cc] Continue reading ...

Copy Table Data Between Servers

This script will copy table data from one environment to another. This script uses a multithreaded clr procedure created by Alan Kaplan here. You can also change the parallel call below if you want to execute serially. [cc lang=”sql”] CREATE PROCEDURE [dba].[pr_refresh_insert_table_data] @linked_server varchar(100), @source_db varchar(100), @target_db varchar(100) AS — dba.pr_refresh_insert_table_data ‘10.10.60.58’, ‘yourdbname’, ‘targetdb’ — SELECT * FROM admindb.dbo.refresh_table_columns BEGIN SET NOCOUNT ON; 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 @TableID int; DECLARE @TableCount int; DECLARE @source varchar(max) = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’ DECLARE @TableName varchar(255); SET @TableName = ”; — Comment out to use list technique below DECLARE @TableList TABLE ( TableID int IDENTITY(1, 1) NOT NULL, TableName varchar(255) ); DECLARE @InsertData Generic_Tt_VarcharMax_5_Col INSERT INTO @TableList(TableName) SELECT DISTINCT table_name FROM admindb.[tmp].[refresh_table_schema_list] tsl WHERE process_data = 1 AND EXISTS (SELECT 1 FROM admindb.tmp.refresh_create_tables WHERE table_name = tsl.table_name) SET @CRLF = CHAR(13) + CHAR(10); SET @Tab = CHAR(9); SET @TableCount = (SELECT COUNT(1) FROM @TableList); SET @TableID = 1; DECLARE @has_identity int SET @TableID = 1; WHILE @TableID

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. [cc lang=”sql”] 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 ‘10.10.60.58’, ‘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 ); CREATE TABLE admindb.tmp.refresh_create_tables ( ID INT identity(1,1), table_name varchar(255), create_table varchar(max) ) SET NOCOUNT ON; SET @CRLF = CHAR(13) + CHAR(10); SET @Tab = CHAR(9); SET @sql = ‘INSERT INTO admindb.dbo.refresh_table_columns_tmp ( RowID, TableName, ColumnName, IsIdentity, DataType, IsDataTypeCast, IsNullable ) SELECT RowID = ROW_NUMBER() OVER(PARTITION BY t.TableName ORDER BY t.IsIdentity DESC, t.ORDINAL_POSITION), TableName, ColumnName, IsIdentity, DataType, IsDataTypeCast, IsNullable FROM ( SELECT DISTINCT TableName = c.TABLE_NAME, ColumnName = c.COLUMN_NAME, IsIdentity = sc.is_identity, DataType = ”” + CASE WHEN LOWER(c.DATA_TYPE) IN (”numeric”, ”decimal”, ”real”) THEN LOWER(c.DATA_TYPE) […] 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: [cc lang=”sql”] 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 = sJOB.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 [/cc]

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. [cc lang=”sql”] 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(‘(./Record/@id)[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 ‘%%’) as x ) as y order by record_id desc [/cc] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!