Skip to content

Commit e4a9f17

Browse files
ktaranovktaranov
committed
Add new useful scripts
1 parent 8aa16c2 commit e4a9f17

3 files changed

Lines changed: 218 additions & 0 deletions

‎Scripts/Checking_Your_Backup_Paths_Exist_and_Automatically_Create_it.sql‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
/*
2+
Author: SQL Undercover
3+
Source link: https://sqlundercover.com/2017/09/13/undercover-toolbox-checking-your-backup-paths-exist-and-automatically-creating-them-if-they-dont/
4+
*/
5+
6+
--variable to hold directory to check
7+
DECLARE @Path NVARCHAR(300) = N'C:\Test\Backups';
8+
9+
IF OBJECT_ID('tempdb..#xp_fileexist_Results') IS NOT NULL DROP TABLE #xp_fileexist_Results;
10+
11+
CREATE TABLE #xp_fileexist_Results (
12+
File_Exists int,
13+
File_is_a_Directory int,
14+
Parent_Directory_Exists int
15+
);
16+
17+
--check if directory exists
18+
INSERT INTO #xp_fileexist_Results
19+
(File_Exists, File_is_a_Directory, Parent_Directory_Exists)
20+
EXEC master.dbo.xp_fileexist @Path;
21+
22+
IF EXISTS (SELECT 1 FROM #xp_fileexist_Results WHERE File_is_a_Directory = 1) --if exists PRINT 'Directory Exists'
23+
PRINT 'Directory Exists'
24+
ELSE --if directory doesn't exist, attempt to create it
25+
BEGIN
26+
EXEC xp_create_subdir @Path;
27+
28+
--perform another existance check to make sure that the directory was actually created
29+
TRUNCATE TABLE #xp_fileexist_Results;
30+
31+
INSERT INTO #xp_fileexist_Results(File_Exists, File_is_a_Directory, Parent_Directory_Exists)
32+
EXEC master.dbo.xp_fileexist @Path;
33+
34+
IF EXISTS (SELECT 1 FROM #xp_fileexist_Results WHERE File_is_a_Directory = 1) --if new directory exists PRINT 'Directory Created'
35+
PRINT 'Directory Created'
36+
ELSE
37+
PRINT 'Error Creating Folder' --if new directory doesn't exist then there must have been a problem creating it
38+
END;
39+
GO

‎Scripts/Find_Synonyms_for_nested_objects_in_the_different_databases.sql‎

Lines changed: 111 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,111 @@
1+
/*
2+
Author: Svetlana Golovko
3+
Source link: https://www.mssqltips.com/sqlservertip/4876/finding-sql-server-object-dependencies-for-synonyms/
4+
*/
5+
6+
SET NOCOUNT ON;
7+
8+
-- check if a database has synonyms to the objects in another database
9+
IF EXISTS (SELECT base_object_name
10+
FROM sys.synonyms
11+
WHERE base_object_name LIKE '%.%.%'
12+
AND LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
13+
CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1) <> DB_NAME()
14+
)
15+
BEGIN
16+
17+
DECLARE @sql NVARCHAR(MAX),
18+
@syn_name NVARCHAR(255),
19+
@db NVARCHAR(255),
20+
@dbid NVARCHAR(20),
21+
@objname NVARCHAR(255)
22+
23+
CREATE TABLE #tempTbl ( syn_name NVARCHAR(255),
24+
syn_base_object NVARCHAR(255),
25+
syn_base_object_db NVARCHAR(255),
26+
nest_level SMALLINT
27+
);
28+
29+
DECLARE SYN_DB CURSOR FOR
30+
31+
-- get list of synonyms to the objects in another database
32+
SELECT DISTINCT [name] AS SYN_NAME,
33+
LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
34+
CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1) AS DBNM,
35+
DB_ID(LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
36+
CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1)) AS DBID,
37+
RIGHT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
38+
CHARINDEX('.', reverse(REPLACE(REPLACE(base_object_name ,'[',''),']','')))-1) AS objectname
39+
FROM sys.synonyms
40+
WHERE base_object_name LIKE '%.%.%'
41+
AND LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
42+
CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1) <> DB_NAME()
43+
44+
OPEN SYN_DB;
45+
46+
FETCH NEXT FROM SYN_DB
47+
INTO @syn_name, @db, @dbid, @objname;
48+
49+
WHILE @@FETCH_STATUS = 0
50+
BEGIN
51+
52+
SET @sql = '
53+
WITH DepTree
54+
AS
55+
(
56+
SELECT ''' + @db + ''' AS DBNAME,
57+
o.[name],
58+
o.[object_id] AS referenced_id ,
59+
o.[name] AS referenced_name,
60+
o.[object_id] AS referencing_id,
61+
o.[name] AS referencing_name,
62+
0 AS NestLevel
63+
FROM [' + @db + '].sys.objects o
64+
WHERE o.is_ms_shipped = 0
65+
66+
UNION ALL
67+
68+
SELECT ''' + @db + ''' AS DBNAME,
69+
r.[name],
70+
d1.referenced_id,
71+
OBJECT_NAME( d1.referenced_id,' + @dbid + ') ,
72+
d1.referencing_id,
73+
OBJECT_NAME( d1.referencing_id,' + @dbid + ') ,
74+
NestLevel + 1
75+
FROM [' + @db + '].sys.sql_expression_dependencies d1 JOIN DepTree r
76+
ON d1.referenced_id = r.referencing_id
77+
)
78+
79+
INSERT INTO #tempTbl
80+
SELECT ''' + @syn_name + ''' AS syn_name,
81+
''' + @objname + ''' AS syn_base_object,
82+
''' + @db + ''' AS syn_base_object_db,
83+
MAX(NestLevel) AS nest_level
84+
FROM DepTree
85+
WHERE referencing_name = ''' + @objname + '''
86+
GROUP BY referenced_name, DBNAME, referencing_name
87+
ORDER BY MAX(NestLevel) DESC'
88+
89+
EXECUTE (@sql);
90+
91+
FETCH NEXT FROM SYN_DB
92+
INTO @syn_name, @db, @dbid, @objname;
93+
END
94+
95+
CLOSE SYN_DB;
96+
DEALLOCATE SYN_DB;
97+
98+
END;
99+
100+
SELECT syn_name
101+
, syn_base_object
102+
, syn_base_object_db
103+
, MAX(nest_level) AS nest_level
104+
FROM #tempTbl
105+
GROUP BY syn_name, syn_base_object, syn_base_object_db
106+
-- comment out next line if you want to see all synonyms' dependent objects regardless the nest level
107+
HAVING MAX(nest_level) > 2;
108+
109+
DROP TABLE #tempTbl;
110+
111+
GO

‎Scripts/Find_Synonyms_for_nested_objects_in_the_same_database.sql‎

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
/*
2+
Author: Svetlana Golovko
3+
Source link: https://www.mssqltips.com/sqlservertip/4876/finding-sql-server-object-dependencies-for-synonyms/
4+
*/
5+
6+
DECLARE @schema SYSNAME;
7+
8+
-- find default user's schema for the one-part base objects names (no schema name)
9+
SELECT @schema = default_schema_name
10+
FROM sys.database_principals
11+
WHERE name = user_name();
12+
13+
-- find all views with their dependencies
14+
WITH DepTree
15+
AS
16+
(
17+
SELECT o.[name],
18+
s.[name] AS oSchema,
19+
o.[object_id] AS referenced_id ,
20+
o.[name] AS referenced_name,
21+
o.[object_id] AS referencing_id,
22+
o.[name] AS referencing_name,
23+
0 AS NestLevel
24+
FROM sys.objects o JOIN sys.schemas s
25+
ON o.[schema_id] = s.[schema_id]
26+
WHERE o.is_ms_shipped = 0
27+
-- comment out next line if you need to check all object types, not only views
28+
AND o.[type] = 'V'
29+
30+
UNION ALL
31+
32+
SELECT r.[name],
33+
r.oSchema,
34+
d1.referenced_id,
35+
OBJECT_NAME( d1.referenced_id) ,
36+
d1.referencing_id,
37+
OBJECT_NAME( d1.referencing_id) ,
38+
NestLevel + 1
39+
FROM sys.sql_expression_dependencies d1 JOIN DepTree r
40+
ON d1.referenced_id = r.referencing_id
41+
)
42+
,
43+
-- find all synonyms in CURRENT database
44+
Syn
45+
AS
46+
(
47+
SELECT [name],
48+
base_object_name,
49+
LTRIM(RTRIM( REPLACE(REPLACE(REPLACE(REPLACE(base_object_name ,'[',''),']',''), DB_NAME()+'..',''), DB_NAME()+'.',''))) as objectname
50+
FROM sys.synonyms s
51+
WHERE (base_object_name like '%.%.%'
52+
AND LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
53+
CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1) = DB_NAME())
54+
OR base_object_name NOT LIKE '%.%.%'
55+
)
56+
57+
SELECT s.[name] AS syn_name,
58+
base_object_name AS syn_base_object,
59+
MAX(NestLevel) AS nest_level
60+
FROM DepTree t JOIN Syn s
61+
ON oSchema + '.' + t.referencing_name =
62+
CASE WHEN s.objectname NOT LIKE '%.%' THEN @schema + '.' + s.objectname
63+
ELSE s.objectname END
64+
GROUP BY base_object_name, s.[name]
65+
-- comment out next line if you want to see all synonyms' dependent objects regardless nest level
66+
HAVING MAX(NestLevel) > 2
67+
ORDER BY MAX(NestLevel) DESC;
68+
GO

0 commit comments

Comments
 (0)