Exploring the MS SQL Server contents from TSQL
There are a set of stored procedures the always start with sp_ and then there is the SQL way of accessing information. I'll try to include both ways here.
List of Databases
List Tables
Other Useful SQL Scripts
1 USE master
2
3 GO
4
5 -- Cleaning up databases from the system.
6
7 SELECT *
8 INTO tmp
9 FROM (
10 SELECT name
11 FROM sys.databases
12 WHERE name like 'A2018%') AS X
13
14 DECLARE cur CURSOR FOR
15 SELECT name FROM tmp;
16
17 OPEN cur;
18 DECLARE @dbname nvarchar(MAX);
19 DECLARE @code nvarchar(MAX);
20
21 FETCH NEXT from cur INTO @dbname;
22 WHILE (@@FETCH_STATUS = 0)
23 BEGIN
24 SELECT @code = 'DROP DATABASE ' + QUOTENAME(@dbname) + '';
25 EXEC sp_executesql @code;
26 FETCH NEXT from cur INTO @dbname;
27 END
28 CLOSE cur;
29 DEALLOCATE cur;
30
31
32 -- Cleaning up Users from the system
33 SELECT *
34 INTO tmpnames
35 FROM (
36 SELECT name
37 FROM master.sys.server_principals
38 WHERE name like 'A2018%') AS X;
39
40 DECLARE cur CURSOR FOR
41 SELECT name FROM tmpnames;
42
43 OPEN cur;
44 DECLARE @dbname nvarchar(MAX);
45 DECLARE @code nvarchar(MAX);
46
47 FETCH NEXT from cur INTO @dbname;
48 WHILE (@@FETCH_STATUS = 0)
49 BEGIN
50 SELECT @code = 'DROP LOGIN ' + QUOTENAME(@dbname) + '';
51 EXEC sp_executesql @code;
52 FETCH NEXT from cur INTO @dbname;
53 END
54 CLOSE cur;
55 DEALLOCATE cur;