Get RowCount from a Dynamically Executed Statement

 

--  Get RowCount from a Dynamically Executed Statement
 
       DECLARE 
		  @Period VARCHAR (8) = '' 
    ,     @DEDFYXXRecords BIGINT = 0 
    ,     @EXECFYXX NVARCHAR (MAX) = ''
    ,     @RowCount BIGINT = 0;  
  
    SELECT @EXECFYXX = ' INSERT Data( ' 
    +    ' Period ' 
    +    ',   CostCenter '     
    +    ') ' 
    +    'SELECT ' 
    +    ' FYXX.Period ' 
    +    ',   FYXX.CostCenter '     
    +    'FROM DataFY'
    +    LEFT (@Period,    2) 
    +    ' FYXX ' 
    +    ' JOIN DataB da ' 
    +    '     ON FYXX.Account = da.Acct ' 
    +    ' WHERE da.GroupID = 99 '
    +    '    AND    FYXX.Period = '''
    +    @Period 
    +    '''; SELECT @RowCount = @@ROWCOUNT; ';
 
    -- EXEC sp_executesql @EXECFYXX, N'@RowCount BIGINT OUTPUT', @RowCount OUTPUT
 
    SELECT @DEDFYXXRecords = @RowCount
    
    SELECT @DEDFYXXRecords 

Tool for Scripting View Generation

 

-- Tool for Scripting View Generation
 
       -- Given two databases, source and dest, this script will generate a READ 
       -- ONLY view in dest for each table or view in source. 
 
       -- http://joeskb.blogspot.com/2013/08/tool-for-scripting-view-generation.html

SSIS/SQLAgent - Enable User to See and Start Jobs

 

-- SSIS/SQLAgent - Enable User to See and Start Jobs
 
       -- To see, the user must be in the SQLAgentReaderRole or SQLAgentUserRole,      
       -- to start them the user must be in the SQLAgentOperaterRole. As these 
       -- only exist in MSDB then the user must also be a member there.

Find tables with foreign keys

 

-- Find tables with foreign keys 
-- Source: http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server 
 
       SELECT 
              t.name TableWithForeignKey
       ,      fk.constraint_column_id FK_PartNo 
       ,      c.name ForeignKeyColumn 
       FROM   sys.foreign_key_columns fk
       JOIN   sys.tables t 
              ON     fk.parent_object_id = t.[object_id] 
       JOIN   sys.columns c 
              ON     fk.parent_object_id = c.[object_id] 
              AND fk.parent_column_id = c.column_id
       JOIN   sys.tables t1
              ON     fk.referenced_object_id = t1.[object_id]  
       ORDER BY
              TableWithForeignKey
       ,      FK_PartNo

What are the sizes of my db's?

 

-- What are the sizes of my db's?
 
       GO
       
       WITH fs
       AS (
              SELECT 
                     m.database_id
              ,      m.[type]
              ,      m.size * 8.0 / 1024 size
              FROM   sys.master_files m 
       )
       SELECT
              name
       ,      (SELECT SUM (size) FROM fs WHERE [type] = 0 AND fs.database_id = db.database_id) DataFileSizeMB
       ,      (SELECT SUM (size) FROM fs WHERE [type] = 1 AND fs.database_id = db.database_id) LogFileSizeMB
       FROM   sys.databases db
 

Delete old backups

 

-- Delete old backups 
 
--     EXECUTE master.dbo.xp_delete_file 0,N'\\Myapp4\SomeSQLBACKUP\MyCoSQL-TEST','N',N'2017-01-05T11:14:40',1

File tools

 

-- File tools 
 
       -- Find the BAK files:
       SELECT TOP 100 
              m.physical_device_name
       ,      b.backup_start_date
       ,      b.backup_finish_date
       ,      b.backup_size/1024.0 BackupSizeKB
       FROM   msdb.dbo.backupset b
       JOIN   msdb.dbo.backupmediafamily m 
              ON     b.media_set_id = m.media_set_id
       WHERE  /* database_name like '%Mysmco%' */ 
              m.physical_device_name LIKE '%bak%' 
       ORDER BY 
              b.backup_finish_date DESC
 
       -- Find the MDF's and LDF's:
       DECLARE @command varchar(1000)
       SELECT @command = 'SELECT * FROM sys.database_files'
       EXEC sp_MSforeachdb @command
 
       -- Or ... easier
       EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename,  SF.size FROM sys.sysfiles SF'
 
       -- Or ... even easier
       SELECT 'USE [' + name + ']; SELECT SF.filename, SF.size FROM sys.sysfiles SF'
       FROM master.dbo.sysdatabases 
 

Force the db offline

 

-- Force the db offline 
 
--     ALTER DATABASE [MyDB-DEV-NEW] SET OFFLINE 
 
-- Same but rollback pending transactions 
 
--     ALTER DATABASE [MyDB-DEV-NEW] SET OFFLINE WITH ROLLBACK IMMEDIATE
 

Set to multi user

 

-- Set to multi user 
 
--     ALTER DATABASE [MyDB-DEV-NEW] SET MULTI_USER;
 
-- Same but rollback pending transactions 
 
--     ALTER DATABASE [MyDB-DEV-NEW] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
 

Set to read only

 

-- ALTER DATABASE [MyDB-DEV-NEW] SET READ_ONLY;