= 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 ==

{{{#!highlight sql
EXEC sp_databases;

-- OR

SELECT name, database_id, create_date
FROM sys.databases;
}}}

== List Tables ==

{{{#!highlight sql

}}}


= Other Useful SQL Scripts =

{{{#!highlight sql 
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;
}}}