sp_CodeGenerator

USE [datastage]
GO
/****** Object:  StoredProcedure [dbo].[sp_CodeGenerator]    Script Date: 09/30/2013 13:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--      Author: John Olsen
-- Create date: 20120323
-- Update date: 20120425
-- Review date: 
-- Description: Generate column related t-sql
--              and c# code for specified table
--              because i have had carpul
--              tunnel surgery once already
-- =============================================
ALTER PROCEDURE [dbo].[sp_CodeGenerator] ( @tabname varchar(128) )
AS
BEGIN
-------------------------------------
--DECLARE @tabname varchar(128)
--SET @tabname = 'truck_transfers'
-------------------------------------
SET NOCOUNT ON;
DECLARE @types TABLE ( xtype tinyint
                     , sql_type varchar(128)
                     , csharp_type varchar(30)
                     , db_type varchar(128)
                     , use_len tinyint
                     , use_prec tinyint
                     , use_scale tinyint
                     , type_class tinyint )
       INSERT INTO @types (xtype, sql_type, csharp_type, db_type, use_len, use_prec, use_scale, type_class)
            SELECT 127, 'bigint', 'int', 'BigInt',0,0,0,1
      UNION SELECT 173, 'binary', 'byte[]', 'Binary',0,0,0,1
      UNION SELECT 104, 'bit', 'int', 'Bit',0,0,0,1
      UNION SELECT 175, 'char', 'string', 'Char',1,1,0,2
      UNION SELECT 40, 'date', 'string', 'VarChar',1,1,0,3
      UNION SELECT 61, 'datetime', 'string', 'DateTime',0,0,0,3
      UNION SELECT 42, 'datetime2', 'string', 'DateTime',0,0,0,3
      UNION SELECT 43, 'datetimeoffset', 'string', 'VarChar',1,1,0,3
      UNION SELECT 106, 'decimal', 'float', 'Decimal',0,1,1,1
      UNION SELECT 62, 'float', 'float', 'Float',0,1,1,1
      UNION SELECT 240, 'geography', 'SqlGeography', 'Geography',0,0,0,4
      UNION SELECT 240, 'geometry', 'SqlGeometry', 'Geometry',0,0,0,4
      UNION SELECT 240, 'hierarchyid', 'string', 'NVarChar',1,1,0,4
      UNION SELECT 34, 'image', 'byte[]', 'Image',0,0,0,4
      UNION SELECT 56, 'int', 'int', 'Int',0,0,0,1
      UNION SELECT 60, 'money', 'float', 'Money',0,0,0,1
      UNION SELECT 239, 'nchar', 'string', 'NChar',1,1,0,2
      UNION SELECT 99, 'ntext', 'string', 'NText',0,0,0,2
      UNION SELECT 108, 'numeric', 'float', 'Decimal',0,1,1,1
      UNION SELECT 231, 'nvarchar', 'string', 'NVarChar',1,1,0,2
      UNION SELECT 59, 'real', 'float', 'Real',0,1,1,1
      UNION SELECT 58, 'smalldatetime', 'string', 'SmallDateTime',0,0,0,3
      UNION SELECT 52, 'smallint', 'int', 'SmallInt',0,0,0,1
      UNION SELECT 122, 'smallmoney', 'float', 'SmallMoney',0,0,0,1
      UNION SELECT 98, 'sql_variant', 'object', 'Variant',0,0,0,4
      UNION SELECT 231, 'sysname', 'string', 'VarChar',1,1,0,2
      UNION SELECT 35, 'text', 'string', 'Text',0,0,0,2
      UNION SELECT 41, 'time', 'string', 'VarChar',0,0,0,3
      UNION SELECT 189, 'timestamp', 'string', 'Timestamp',0,0,0,3
      UNION SELECT 48, 'tinyint', 'int', 'TinyInt',0,0,0,1
      UNION SELECT 36, 'uniqueidentifier', 'SqlGuid', 'UniqueIdentifier',0,0,0,4
      UNION SELECT 165, 'varbinary', 'byte[]', 'VarBinary',1,1,0,4
      UNION SELECT 167, 'varchar', 'string', 'VarChar',1,1,0,2
      UNION SELECT 241, 'xml', 'string', 'Text',0,0,0,2
--SELECT * FROM @types
DECLARE @cols TABLE ( id int identity(1,1)
                    , colname varchar(128)
                    , parmname varchar(128)
                    , sql_type varchar(128)
                    , db_type varchar(128)
                    , csharp_type varchar(30)
                    )
DECLARE @i int
      , @crlf char(2)
      , @classname varchar(128)
      , @colname varchar(128)
      , @parmname varchar(128)
      , @sql_type varchar(128)
      , @db_type varchar(128)
      , @csharp_type varchar(30)
      , @s_dec varchar(8000)
      , @s_assn varchar(8000)
      , @s_assnB varchar(8000)
      , @s_collist varchar(8000)
      , @s_parmlist varchar(8000)
      , @c_dec varchar(8000)
      , @c_update varchar(8000)
      , @c_pop varchar(8000)
      , @c_parm varchar(8000)
      
SET @crlf = char(13) + char(10)
     INSERT INTO @cols ( colname, parmname, sql_type, db_type, csharp_type )
          SELECT B.name "colname"
               , '@' + B.name "parmname"
               , (case when C.use_scale = 1 then C.sql_type + '(' + cast(B.prec as varchar(30)) + ',' + cast(B.scale as varchar(30)) + ')'
                       when C.use_prec = 1 then C.sql_type + '(' + (case when B.prec = -1 then 'max' else cast(B.prec as varchar(30)) end) + ')'
                       else C.sql_type end) "sql_type"
               , (case when C.use_len = 1 and B.prec > 0 then 'SqlDbType.' + C.db_type + ', ' + cast(B.prec as varchar(30))
                       else 'SqlDbType.' + C.db_type end) "db_type"
               , C.csharp_type
            FROM sysobjects A (NOLOCK)
      INNER JOIN syscolumns B (NOLOCK)
              ON A.id = B.id
      INNER JOIN @types C
              ON B.xtype = C.xtype
           WHERE A.type = 'U'
             AND A.name = @tabname
        ORDER BY A.name ASC
               , B.colorder DESC
          SELECT @i = max(id) 
            FROM @cols
   SET @classname = dbo.fnTitleCase(@tabname)
   SET @s_parmlist = ''
   SET @s_collist = ''
   SET @s_dec = ''
   SET @s_assn = ''
   SET @s_assnB = ''
   SET @c_dec = '   public class ' + @classname + 'DAO : DAO' + @crlf + '   {'
   SET @c_pop = '      public void populate(DataRow row)' + @crlf + '      {'
   SET @c_update = '      public void update()' + @crlf + '      {' + @crlf + '         SqlCommand oCmd = new SqlCommand();' + @crlf + '         oCmd.CommandType = CommandType.StoredProcedure;' + @crlf + '         oCmd.CommandText = "pUpdate";'
   SET @c_parm = ''
   WHILE @i > 0
    BEGIN
       SELECT @colname = A.colname
            , @parmname = A.parmname
            , @sql_type = A.sql_type
            , @db_type = A.db_type
            , @csharp_type = A.csharp_type
         FROM @cols A
        WHERE A.id = @i
        SET @s_collist += @crlf + '   , ' + @colname
        SET @s_parmlist += @crlf + '   , ' + @parmname
        SET @s_dec += @crlf + '   , ' + @parmname + ' ' + @sql_type
        SET @s_assn += @crlf + '   , A.' + @colname + ' = ' + @parmname
        SET @s_assnB += @crlf + '   , A.' + @colname + ' = B.' + @colname
        SET @c_dec += @crlf + '      public ' + @csharp_type + ' ' + @colname + (case when @csharp_type = 'string' then ' = "";' else ' = 0;' end)
        SET @c_pop += @crlf + '         this.' + @colname + ' = ' 
                   + (case when @csharp_type in ('float','int') then @csharp_type + '.Parse(row["' + @colname + '"].ToString());'
                           when @csharp_type in ('string') then 'row["' + @colname + '"].ToString();'
                           else '('+@csharp_type+') row["' + @colname + '"];' end )
        SET @c_update += @crlf + '         oCmd.Parameters.Add("' + @parmname + '", ' + @db_type + ').Value = this.' + @colname + ';'
       SET @i -= 1
    END
   PRINT @s_collist + @crlf
   PRINT @s_parmlist + @crlf
   PRINT @s_dec + @crlf
   PRINT @s_assn + @crlf + @crlf
   PRINT @s_assnB + @crlf + @crlf
   PRINT @c_dec + @crlf + @crlf
   PRINT @c_pop + @crlf +  '      }//populate()' + @crlf + @crlf
   PRINT @c_update + @crlf +  '      }//update()' + @crlf + @crlf
   PRINT @c_parm + @crlf
   PRINT '   } //' + @classname
END --sp_CodeGenerator()

Scripted Backups Example 2

USE master 
GO

DECLARE @theday char(1)
      , @file varchar(128)

   SET @theday = datepart(dw, getdate())

   ALTER DATABASE dbname SET RECOVERY SIMPLE;

   SET @file = 'D:\BACKUPS\dbname_' + @theday + '.bak';

   BACKUP DATABASE dbname TO DISK = @file WITH INIT;

   BACKUP LOG dbname WITH TRUNCATE_ONLY;

 GO

This does a 7 day "rolling backup", overwriting the backup from last week. Set it up on a nightly job.

Scripted Backups Example 1

USE master GO DECLARE @dbs AS TABLE ( id int identity(1,1), dbname sysname ) DECLARE @id int , @dbname sysname , @path varchar(128) , @file nvarchar(255) , @theday char(1) SET @path = 'D:\DEV_BACKUPS' SET @theday = datepart(dw, CURRENT_TIMESTAMP) INSERT INTO @dbs ( dbname ) SELECT name FROM sys.databases WHERE database_id > 4 --not system dbs AND state = 0 --online ORDER BY name SELECT @id = max(id) FROM @dbs WHILE @id > 0 BEGIN SELECT @dbname = dbname FROM @dbs WHERE id = @id SET @file = @path + '\' + @dbname + '_BAK' + @theday + '.bak' BACKUP DATABASE @dbname TO DISK = @file WITH INIT; BACKUP LOG @dbname WITH TRUNCATE_ONLY; SET @id = @id - 1 END GO

Drop all constraints (PKs & FKs) and Indexes

DECLARE @objs TABLE ( id int identity(1,1), cmd nvarchar(4000), srt tinyint )
DECLARE @id int
      , @cmd nvarchar(4000)

   INSERT INTO @objs (cmd,srt)
        SELECT 'DROP INDEX [' + A.name + '] ON [' +  + SCHEMA_NAME(B.schema_id) + + '].[' + B.name + ']'
             , 1
          FROM sys.indexes  A 
    INNER JOIN sys.objects  B
            ON A.object_id = B.object_id
         WHERE A.is_primary_key = 0
           AND A.is_unique_constraint = 0
           AND B.type = 'U'
           AND A.name IS NOT NULL
   UNION
        SELECT 'ALTER TABLE [' +  + SCHEMA_NAME(B.schema_id) + + '].[' + B.name + '] DROP CONSTRAINT [' + A.name + ']'
             , 2
          FROM sys.objects  A 
    INNER JOIN sys.objects  B
            ON A.parent_object_id = B.object_id
         WHERE A.type = 'PK'
            OR A.name IN ( 'PK_deemed_raiv','PK_driverchecklog')
      ORDER BY 2

SELECT @id = max(id) FROM @objs

WHILE @id > 0
 BEGIN
   SELECT @cmd = cmd FROM @objs WHERE id = @id
   EXEC sp_executesql @cmd
   SET @id = @id - 1
 END
GO

sp_findit

use master
go

CREATE PROCEDURE [sp_findit] ( @value sysname ) 
AS
BEGIN

   SET NOCOUNT ON;

          SELECT distinct so.type
               , so.name
            FROM syscomments sc
      INNER JOIN sysobjects so
              ON sc.id=so.id
           WHERE sc.TEXT LIKE '%' + @value + '%'
           UNION
          SELECT distinct so.type
               , so.name
            FROM syscolumns sc
      INNER JOIN sysobjects so
              ON sc.id = so.id
           WHERE sc.name LIKE '%' + @value + '%'
        ORDER BY 1
               , 2

END --sp_findit()