Cursor

MSSQL Set All Databases to FULL Recovery

/ MSSQL Server /

This script sets all databases to FULL recovery (except for master, model and tempdb). We can avoid using a cursor by making use of a temporary table. First a temptable gets filled with execution commands, and then the script executes each row until the record id equals the total number of records.

-- Declarations and temptables...
DECLARE @iNumRecs int, @iRecNum int = 1, @vCommand nvarchar(max)
CREATE TABLE #tCommands (ID int IDENTITY(1,1), Command nvarchar(max))

-- Insert Commands into temptable...
INSERT INTO #tCommands (Command)
SELECT N'ALTER DATABASE [' + name + '] SET RECOVERY FULL WITH NO_WAIT' 
FROM [master].[sys].[databases]
WHERE recovery_model_desc = 'SIMPLE' AND name NOT in ('master', 'msdb', 'tempdb')

-- Execute commands from temptable...
SELECT @iNumRecs = COUNT(*) FROM #tCommands
WHILE @iRecNum <= @iNumRecs
BEGIN
 SELECT @vCommand = Command FROM #tCommands
 WHERE ID = @iRecNum
 -- Execute command and increase record number...
 -- EXECUTE(@vCommand) --Alternative, Not preferred...
 EXECUTE sys.sp_executesql @stmt = @vCommand
 SET @iRecNum += 1
END

-- Cleanup...
DROP TABLE #tCommands

MSSQL Retreive Column Info From Tables

/ MSSQL Server /

With this script you can retrieve column info (datatype, length, precision, scale and collation) from all tables in a specific database.

-- Retrieve column info from all tables in a database...
USE [Databasename]
GO

-- Declare variables...
DECLARE @vTableName AS nvarchar(256)

-- Create temptable...
CREATE TABLE #tDBColumnInfo (
  [tablename] [varchar] (100) NULL,
  [columnname] [varchar] (100) NULL,
  [datatype] [varchar] (50) NULL,
  [length] [int] NULL,
  [xprec] [int] NULL,
  [xscale] [int] NULL,
  [collation] [varchar] (100) NULL
  ) 

-- Get Tablenames...
DECLARE curTableNames CURSOR FOR
SELECT [name] from sys.tables

-- Get columns from table...
OPEN curTableNames
  FETCH NEXT FROM curTableNames INTO @vTableName
  WHILE @@FETCH_STATUS = 0   
BEGIN
  INSERT INTO #tDBColumnInfo
  SELECT 
       @vTableName AS 'tablename',
       sc.[name] AS [columnname], 
       st.[name] AS [datatype], 
       sc.[length], 
       sc.[xprec], 
       sc.[xscale], 
       sc.[collation] 
  FROM syscolumns sc
  LEFT OUTER JOIN systypes st on sc.xtype = st.xtype
  WHERE id = ( SELECT id FROM sysobjects 
               WHERE [type] = 'U' 
               AND [Name] = @vTableName )
  FETCH NEXT FROM curTableNames INTO @vTableName
END

-- Cleanup cursor...
CLOSE curTableNames
DEALLOCATE curTableNames

-- Display Result and cleanup temptable...
SELECT * FROM #tDBColumnInfo
DROP Table #tDBColumnInfo