I'm getting a little tired of always clicking around in SSMS. Here goes a shot at powershell. I've done a few tutorials on powershell, but don't use it enough to remember. The only thing I remember is Get-Child-Items and it's alias GCI. That's a start. Follow along with me.
User … Continue Reading ››
Monthly Archives: December 2010
See all roles on your server
This cursor inserts all of the roles and members from each database into your admin database.
USE [SQL_DBA_Sandbox]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Roles](
[DBName] [varchar](max) NULL,
[Role] [varchar](max) NULL,
[Member] [varchar](max) NULL
) ON [PRIMARY]
END
ELSE
Continue Reading ››See all the schemas on your server
Oh no! A useful cursor?
DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;
CREATE TABLE #RESULTS
(TABLE_CATALOG sysname,
TABLE_SCHEMA sysname)
DECLARE @SQL VARCHAR(2000);
DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR
SELECT name FROM sys.databases
WHERE database_id > 4
OPEN Cursor_DB
FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE … Continue Reading ››