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

   1 EXEC sp_databases;
   2 
   3 -- OR
   4 
   5 SELECT name, database_id, create_date
   6 FROM sys.databases;

List Tables

   1 

Other Useful SQL Scripts

USE master

GO

-- Cleaning up databases from the system.

SELECT *
INTO tmp 
FROM (
SELECT name
FROM sys.databases
WHERE name like 'A2018%') AS X

DECLARE cur CURSOR FOR 
    SELECT name FROM tmp;

OPEN cur;
DECLARE @dbname nvarchar(MAX);
DECLARE @code nvarchar(MAX);

FETCH NEXT from cur INTO @dbname;
WHILE (@@FETCH_STATUS = 0)
    BEGIN  
        SELECT @code = 'DROP DATABASE ' + QUOTENAME(@dbname) + '';
        EXEC sp_executesql @code;
        FETCH NEXT from cur INTO @dbname;
    END
CLOSE cur;
DEALLOCATE cur;


-- Cleaning up Users from the system
SELECT * 
INTO tmpnames 
FROM (
    SELECT name
    FROM master.sys.server_principals
    WHERE name like 'A2018%') AS X;

DECLARE cur CURSOR FOR 
    SELECT name FROM tmpnames;

OPEN cur;
DECLARE @dbname nvarchar(MAX);
DECLARE @code nvarchar(MAX);

FETCH NEXT from cur INTO @dbname;
WHILE (@@FETCH_STATUS = 0)
    BEGIN  
        SELECT @code = 'DROP LOGIN ' + QUOTENAME(@dbname) + '';
        EXEC sp_executesql @code;
        FETCH NEXT from cur INTO @dbname;
    END
CLOSE cur;
DEALLOCATE cur;