Skip to content

Commit 6712f8b

Browse files
author
Michelle Ufford
committed
open sourcing scripts
1 parent e20ba65 commit 6712f8b

16 files changed

Lines changed: 2369 additions & 0 deletions

‎admin/dba_recompile_sp.sql‎

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,123 @@
1+
Use dbaTools;
2+
Go
3+
4+
If ObjectProperty(Object_ID('dbo.dba_recompile_sp'), N'IsProcedure') Is Null
5+
Begin
6+
Execute ('Create Procedure dbo.dba_recompile_sp As Print ''Hello World!''')
7+
RaisError('Procedure dba_recompile_sp created.', 10, 1);
8+
End;
9+
Go
10+
11+
Set ANSI_Nulls On;
12+
Set Quoted_Identifier On;
13+
Go
14+
15+
Alter Procedure dbo.dba_recompile_sp
16+
17+
/* Declare Parameters */
18+
@databaseName nvarchar(128) = Null /* Null = all databases */
19+
, @tableName nvarchar(128) = Null /* Null = all tables */
20+
21+
As
22+
/**********************************************************************************************************
23+
24+
NAME: dba_recompile_sp
25+
26+
SYNOPSIS: Recompiles all procs in a specific database or all procs; can recompile a specific table, too.
27+
28+
DEPENDENCIES: The following dependencies are required to execute this script:
29+
- SQL Server 2005 or newer
30+
31+
AUTHOR: Michelle Ufford, http://sqlfool.com
32+
33+
CREATED: 2009-09-12
34+
35+
VERSION: 1.0
36+
37+
LICENSE: Apache License v2
38+
39+
----------------------------------------------------------------------------
40+
DISCLAIMER:
41+
This code and information are provided "AS IS" without warranty of any kind,
42+
either expressed or implied, including but not limited to the implied
43+
warranties or merchantability and/or fitness for a particular purpose.
44+
----------------------------------------------------------------------------
45+
46+
---------------------------------------------------------------------------------------------------------
47+
-- DATE VERSION AUTHOR DESCRIPTION --
48+
---------------------------------------------------------------------------------------------------------
49+
20150619 1.0 Michelle Ufford Open Sourced on GitHub
50+
**********************************************************************************************************/
51+
52+
Set NoCount On;
53+
Set XACT_Abort On;
54+
Set Ansi_Padding On;
55+
Set Ansi_Warnings On;
56+
Set ArithAbort On;
57+
Set Concat_Null_Yields_Null On;
58+
Set Numeric_RoundAbort Off;
59+
60+
Begin
61+
62+
/* Make sure the global temp tables do not already exist, i.e. failed execution */
63+
If Exists(Select * From tempdb.sys.tables Where name = '###databaseList')
64+
Drop Table #databaseList;
65+
66+
If Exists(Select * From tempdb.sys.tables Where name = '##tableList')
67+
Drop Table tableList;
68+
69+
/* Declare Temp Tables */
70+
Create Table ##databaseList
71+
(
72+
databaseName nvarchar(128)
73+
, processed bit
74+
);
75+
76+
Create Table ##tableList
77+
(
78+
databaseName nvarchar(128)
79+
, tableName nvarchar(128)
80+
, processed bit
81+
);
82+
83+
Insert Into ##databaseList
84+
Select name As databaseName
85+
, 0 As processed
86+
From sys.databases
87+
Where name = IsNull(@databaseName, name);
88+
89+
While Exists(Select Top 1 databaseName From ##databaseList Where processed = 0)
90+
Begin
91+
92+
Execute sp_msforeachdb 'Use ?;
93+
Select name As tableName
94+
Into ##tableList
95+
From sys.tables
96+
Where name = IsNull(@tableName, name);
97+
98+
Declare @tableName nvarchar(128) = (Select Top 1 tableName From #tableList);
99+
100+
While Exists(Select Top 1 * From #tableList)
101+
Begin
102+
Execute sp_recompile @tableName;
103+
Delete From #tableList Where tableName = @tableName;
104+
Select Top 1 @tableName = tableName From #tableList Order By tableName;
105+
End;
106+
107+
Drop Table ##tableList;'
108+
109+
End
110+
111+
Set NoCount Off;
112+
Return 0;
113+
End
114+
Go
115+
116+
Set Quoted_Identifier Off;
117+
Go
118+
119+
If ObjectProperty(Object_ID('dbo.dba_recompile_sp'), N'IsProcedure') = 1
120+
RaisError('Procedure dba_recompile_sp was successfully updated.', 10, 1);
121+
Else
122+
RaisError('Procedure dba_recompile_sp FAILED to create!', 16, 1);
123+
Go

‎dev/bcp_script_generator.sql‎

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,103 @@
1+
/**********************************************************************************************************
2+
3+
NAME: bcp_script_generator.sql
4+
5+
SYNOPSIS: Generates bcp scripts using SQL Server metadata
6+
7+
DEPENDENCIES: The following dependencies are required to execute this script:
8+
- SQL Server 2005 or newer
9+
10+
AUTHOR: Michelle Ufford, http://sqlfool.com
11+
12+
CREATED: 2012-05-17
13+
14+
VERSION: 1.0
15+
16+
LICENSE: Apache License v2
17+
18+
----------------------------------------------------------------------------
19+
DISCLAIMER:
20+
This code and information are provided "AS IS" without warranty of any kind,
21+
either expressed or implied, including but not limited to the implied
22+
warranties or merchantability and/or fitness for a particular purpose.
23+
----------------------------------------------------------------------------
24+
25+
---------------------------------------------------------------------------------------------------------
26+
-- DATE VERSION AUTHOR DESCRIPTION --
27+
---------------------------------------------------------------------------------------------------------
28+
20150619 1.0 Michelle Ufford Open Sourced on GitHub
29+
**********************************************************************************************************/
30+
31+
-- User-defined variables --
32+
33+
DECLARE @tableToBCP NVARCHAR(128) = 'sandbox.dbo.example_table'
34+
, @Top VARCHAR(10) = NULL -- Leave NULL for all rows
35+
, @Delimiter VARCHAR(4) = '|'
36+
, @UseNULL BIT = 1
37+
, @OverrideChar CHAR(1) = '~'
38+
, @MaxDop CHAR(1) = '1'
39+
, @Directory VARCHAR(256) = 'D:\dba\mufford\scripts';
40+
41+
42+
-- Script-defined variables --
43+
44+
DECLARE @columnList TABLE (columnID INT);
45+
46+
DECLARE @bcpStatement NVARCHAR(MAX) = 'BCP "SELECT '
47+
, @currentID INT
48+
, @firstID INT;
49+
50+
INSERT INTO @columnList
51+
SELECT column_id
52+
FROM sys.columns
53+
WHERE object_id = OBJECT_ID(@tableToBCP)
54+
ORDER BY column_id;
55+
56+
IF @Top IS NOT NULL
57+
SET @bcpStatement = @bcpStatement + 'TOP (' + @Top + ') ';
58+
59+
SELECT @firstID = MIN(columnID) FROM @columnList;
60+
61+
WHILE EXISTS(SELECT * FROM @columnList)
62+
BEGIN
63+
64+
SELECT @currentID = MIN(columnID) FROM @columnList;
65+
66+
IF @currentID <> @firstID
67+
SET @bcpStatement = @bcpStatement + ',';
68+
69+
SELECT @bcpStatement = @bcpStatement +
70+
CASE
71+
WHEN user_type_id IN (231, 167, 175, 239)
72+
THEN 'CASE WHEN ' + name + ' = '''' THEN '
73+
+ CASE
74+
WHEN is_nullable = 1 THEN 'NULL'
75+
ELSE '''' + REPLICATE(@OverrideChar, max_length) + ''''
76+
END
77+
+ ' WHEN ' + name + ' LIKE ''%' + @Delimiter + '%'''
78+
+ ' OR ' + name + ' LIKE ''%'' + CHAR(9) + ''%''' -- tab
79+
+ ' OR ' + name + ' LIKE ''%'' + CHAR(10) + ''%''' -- line feed
80+
+ ' OR ' + name + ' LIKE ''%'' + CHAR(13) + ''%''' -- carriage return
81+
+ ' THEN '
82+
+ CASE
83+
WHEN is_nullable = 1 THEN 'NULL'
84+
ELSE '''' + REPLICATE(@OverrideChar, max_length) + ''''
85+
END
86+
+ ' ELSE ' + name + ' END'
87+
ELSE name
88+
END
89+
FROM sys.columns
90+
WHERE object_id = OBJECT_ID(@tableToBCP)
91+
AND column_id = @currentID;
92+
93+
DELETE FROM @columnList WHERE columnID = @currentID;
94+
95+
96+
END;
97+
98+
SET @bcpStatement = @bcpStatement + ' FROM ' + @tableToBCP
99+
+ ' WITH (NOLOCK) OPTION (MAXDOP 1);" queryOut '
100+
+ @Directory + REPLACE(@tableToBCP, '.', '_') + '.dat -S' + @@SERVERNAME
101+
+ ' -T -t"' + @Delimiter + '" -c -C;'
102+
103+
SELECT @bcpStatement;

‎dev/insert_statement_generator.sql‎

Lines changed: 145 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,145 @@
1+
/**********************************************************************************************************
2+
3+
NAME: insert_statement_generator.sql
4+
5+
SYNOPSIS: Generates insert statements for Teradata using SQL Server metadata.
6+
This is useful for easily migrating small tables (i.e. < 1000 rows)
7+
from SQL Server to Teradata. DO NOT use on large tables.
8+
9+
DEPENDENCIES: The following dependencies are required to execute this script:
10+
- SQL Server 2005 or newer
11+
12+
AUTHOR: Michelle Ufford, http://sqlfool.com
13+
14+
CREATED: 2012-07-26
15+
16+
VERSION: 1.0
17+
18+
LICENSE: Apache License v2
19+
20+
----------------------------------------------------------------------------
21+
DISCLAIMER:
22+
This code and information are provided "AS IS" without warranty of any kind,
23+
either expressed or implied, including but not limited to the implied
24+
warranties or merchantability and/or fitness for a particular purpose.
25+
----------------------------------------------------------------------------
26+
27+
---------------------------------------------------------------------------------------------------------
28+
-- DATE VERSION AUTHOR DESCRIPTION --
29+
---------------------------------------------------------------------------------------------------------
30+
20150619 1.0 Michelle Ufford Open Sourced on GitHub
31+
**********************************************************************************************************/
32+
33+
-- User-defined variables --
34+
DECLARE
35+
@tableName NVARCHAR(128) = 'dbo.example_table'
36+
, @Top VARCHAR(10) = 1000 -- Leave NULL for all rows
37+
, @Execute BIT = 1
38+
, @GenerateSchema BIT = 1
39+
, @GenerateTruncate BIT = 1
40+
, @TeradataDatabase VARCHAR(30) = 'mufford'
41+
, @TeradataTable VARCHAR(30) = NULL -- Will generate if you leave NULL
42+
43+
-- Script-defined variables --
44+
45+
DECLARE @columnList TABLE (columnID INT);
46+
DECLARE @TeradataTableName VARCHAR(60);
47+
48+
IF @TeradataTable IS NULL
49+
SET @TeradataTableName = @TeradataDatabase + '.tmp_' + SUBSTRING(@tableName,PATINDEX('%.%',@tableName)+1,26);
50+
ELSE
51+
SET @TeradataTableName = @TeradataDatabase + '.' + @TeradataTable;
52+
53+
DECLARE @insertStatement NVARCHAR(MAX) = '' --= 'SELECT '
54+
, @columnStatement NVARCHAR(MAX) = 'INSERT INTO ' + @TeradataTableName + ' ('
55+
, @schemaStatement NVARCHAR(MAX) = 'CREATE TABLE ' + @TeradataTableName + '('
56+
, @currentID INT
57+
, @firstID INT;
58+
59+
INSERT INTO @columnList
60+
SELECT column_id
61+
FROM sys.columns
62+
WHERE object_id = OBJECT_ID(@tableName)
63+
ORDER BY column_id;
64+
65+
SELECT @firstID = MIN(columnID) FROM @columnList;
66+
67+
WHILE EXISTS(SELECT * FROM @columnList)
68+
BEGIN
69+
70+
SELECT @currentID = MIN(columnID) FROM @columnList;
71+
72+
IF @currentID <> @firstID
73+
BEGIN
74+
SELECT
75+
@columnStatement = @columnStatement + ','
76+
, @schemaStatement = @schemaStatement + ','
77+
, @insertStatement = @insertStatement + '+'',''+';
78+
END
79+
80+
SELECT @columnStatement = @columnStatement + '"' + SUBSTRING(name, 1, 30) + '"'
81+
FROM sys.columns
82+
WHERE object_id = OBJECT_ID(@tableName)
83+
AND column_id = @currentID;
84+
85+
SELECT @schemaStatement = @schemaStatement + '"' + SUBSTRING(c.name, 1, 30) + '" '
86+
+ CASE
87+
WHEN t.name = 'BIT' THEN 'BYTEINT'
88+
WHEN t.name = 'TINYINT' THEN 'SMALLINT'
89+
WHEN t.name = 'UNIQUEIDENTIFIER' THEN 'CHAR(38)'
90+
WHEN t.name = 'DATETIME' THEN 'TIMESTAMP(3)'
91+
WHEN t.name = 'MONEY' THEN 'DECIMAL(18,4)'
92+
WHEN t.name = 'XML' THEN 'CLOB'
93+
WHEN t.name IN ('SMALLDATETIME', 'DATETIME2') THEN 'TIMESTAMP(0)'
94+
WHEN t.name IN ('NVARCHAR','NCHAR')
95+
THEN SUBSTRING(t.name, 2, 10) + '(' + CAST(c.max_length / 2 AS VARCHAR(4)) + ') CHARACTER SET UNICODE NOT CASESPECIFIC'
96+
WHEN t.name IN ('VARCHAR','CHAR')
97+
THEN t.name + '(' + CAST(c.max_length AS VARCHAR(4)) + ')'
98+
ELSE t.name
99+
END
100+
+ CASE
101+
WHEN c.is_nullable = 1 THEN ' NULL'
102+
ELSE ' NOT NULL'
103+
END
104+
FROM sys.columns AS c
105+
JOIN sys.types AS t
106+
ON c.system_type_id = t.system_type_id
107+
WHERE c.object_id = OBJECT_ID(@tableName)
108+
AND c.column_id = @currentID;
109+
110+
SELECT DISTINCT @insertStatement = @insertStatement
111+
+ 'CASE WHEN ' + QUOTENAME(c.name) + ' IS NULL THEN ''NULL'' ELSE ' +
112+
+ CASE
113+
WHEN t.name IN ('tinyint','smallint','int','real','float','bit','decimal','numeric','smallmoney','bigint') /* number-based columns */
114+
THEN 'CAST(' + QUOTENAME(c.name) + ' AS VARCHAR(' + CAST(c.precision AS VARCHAR(10)) + '))'
115+
WHEN t.name IN ('datetime', 'date', 'datetime2', 'smalldatetime') /* date-based columns */
116+
THEN '''''''''+' + 'CONVERT(VARCHAR(23),' + QUOTENAME(c.name) + ',126)' + '+'''''''''
117+
WHEN t.name IN ('uniqueidentifier') /* guid columns */
118+
THEN '''''''''+' + 'CAST(' + QUOTENAME(c.name) + ' AS CHAR(36))' + '+'''''''''
119+
WHEN t.name IN ('XML') /* xml columns */
120+
THEN '''''''''+' + 'CAST(' + QUOTENAME(c.name) + ' AS VARCHAR(MAX))' + '+'''''''''
121+
ELSE '''''''''+REPLACE(' + QUOTENAME(c.name) + ','''''''','''''''''''')+''''''''' --'''+''''''+''' /* character-based columns */
122+
END
123+
+ ' END '
124+
FROM sys.columns AS c
125+
JOIN sys.types AS t
126+
ON c.system_type_id = t.system_type_id
127+
WHERE c.object_id = OBJECT_ID(@tableName)
128+
AND c.column_id = @currentID;
129+
130+
DELETE FROM @columnList WHERE columnID = @currentID;
131+
132+
END;
133+
134+
SET @insertStatement = 'SELECT ' + CASE WHEN @Top IS NOT NULL THEN 'TOP (' + @Top + ') ' ELSE '' END + '''' + @columnStatement + ') VALUES (''+' + @insertStatement + '+'');'' FROM ' + @tableName + ' WITH (NOLOCK);';
135+
136+
IF @GenerateSchema = 1
137+
SELECT @schemaStatement + ');' AS 'Execute this statement in Teradata to create the table:'
138+
139+
IF @GenerateTruncate = 1
140+
SELECT 'DELETE FROM ' + @TeradataTableName + ';' AS 'Execute this statement in Teradata to truncate the table:'
141+
142+
IF @Execute = 1
143+
EXECUTE sp_executeSQL @insertStatement;
144+
ELSE
145+
SELECT @insertStatement AS 'Execute this statement in SQL Server to generate commands:';

0 commit comments

Comments
 (0)