Table log Details

IF   EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Logtable]') AND type in (N'U'))
BEGIN
     DROP TABLE [Logtable]
END
GO
IF  NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Logtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Logtable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [D] [Nvarchar] (100) NOT NULL,
    [TableName] [Nvarchar](100) NOT NULL,
    [ArchivalTableName] [Nvarchar](100) NOT NULL,
 [StartTime] DateTime  NULL,
 [EndTime] DateTime  NULL,
 [RowCount] BIGINT NULL,
 [Status] [Nvarchar] (50) NULL,
 [Error Message] [Nvarchar](MAX) NULL
 )
END

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'SPName')
BEGIN
DROP PROCEDURE [dbo].[SPName]
END
GO 
CREATE PROCEDURE [dbo].[SPName]  
@D NVARCHAR(100)  
AS  
  BEGIN  
      SET nocount ON;  
 
      BEGIN try  
   IF  NOT EXISTS (SELECT 1  FROM   [tableName]WHERE [D] = @D)
        BEGIN
           PRINT'Invalid Dataset .Please check provided D '
     RETURN;
     END
          IF Object_id('TempDB.dbo.#PurgeTableList') IS NOT NULL  
            BEGIN  
                DROP TABLE #Ptablelist  
            END  
          --Create Temp table  
          CREATE TABLE #Ptablelist  
            (  
               [id]                [INT] IDENTITY(1, 1) NOT NULL,  
               [D]           [NVARCHAR] (100) NOT NULL,  
               [tablename]         [NVARCHAR](100) NOT NULL,  
               [archivaltablename] [NVARCHAR](100) NOT NULL,  
               [query]        [NVARCHAR](max) NOT NULL  
            )  
         --Load Data to Temp table From  
          INSERT INTO #Ptablelist  
                      ([D],  
                       [tablename],  
                       [archivaltablename],  
                       [query])  
          SELECT DISTINCT [D],  
                          [tablename],  
                          [archivaltablename],  
                          [query]  
          FROM   [tableName]  
          WHERE  [status] = 1  
                 AND [D] = @D  
     ORDER BY [tablename] 
 
 
         --Declare Variable  
          DECLARE @LoopCounter       INT,  
                  @MaxId             INT,  
                  @TableName         NVARCHAR(100),  
                  @ArchivalTableName NVARCHAR(100),  
                  @PurgeLogID        INT,  
                  @PurgeQuery        NVARCHAR(max),  
                  @Count             [BIGINT] = 0  
 
          SELECT @LoopCounter = Min(id),  
                 @MaxId = Max(id)  
          FROM   #Ptablelist  
         --Apply P using While loop 
          WHILE( @LoopCounter IS NOT NULL  
                 AND @LoopCounter <= @MaxId )  
            BEGIN  
                SELECT @PurgeQuery = query,  
                       @TableName = tablename,  
                       @ArchivalTableName = archivaltablename,  
                       @D = D  
                FROM   #Ptablelist  
                WHERE  id = @LoopCounter  
              
                --ADD Purge information to Log table   
               
    Insert INTO [Logtable] ([D],[TableName],[ArchivalTableName],[StartTime],[Status])
                                   Values(@D,@TableName,@ArchivalTableName,Getdate(),'In progress')
                SELECT @PurgeLogID = Scope_identity();  
   
                SET @Count=0
              
               --Execute Delete Query    
                EXEC (@PurgeQuery)  
 
                SET @Count = @@rowcount  
 
                --Update Logtable table with RowCount,EndTime  
                UPDATE [Logtable]  
                SET    [rowcount] = @Count,  
                       [endtime] = Getdate(),
        [status] = 'Completed'
                FROM   [Logtable]  
                WHERE  [id] = @PurgeLogID  
 
               
 
                SET @LoopCounter = @LoopCounter + 1  
            END  
      END try  
 
      BEGIN catch  
   --Add Error information to log table
          UPDATE [Logtable]  
          SET    [rowcount] = @Count,  
                 [endtime] = Getdate(),  
                 [status] = 'Error',  
                 [error message] = Error_message()  
          FROM   [PURGELOGINFO]  
          WHERE  [id] = @PurgeLogID  
 
          RETURN;  
      END catch  
  END
  GO

search text of all SQL Server database objects

/* Reto Egeter, fullparam.wordpress.com */

DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%Sale01%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
IF @TableName IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
INSERT INTO @ColumnNameTable
EXEC (@sql)
WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
BEGIN
PRINT @ColumnName
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
INSERT INTO #Results
EXEC(@sql)
IF @@ROWCOUNT > 0 IF @FullRowResult = 1
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
EXEC(@sql)
END
DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
END
END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

Find text inside object

SELECT @SEARCHSTRING = 'text', @notcontain = ''
SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0
or CHARINDEX(@notcontain,syscomments.text)<>0))

Find text in Stored Procedures, View, Trigger, and Function

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)
SELECT @SEARCHSTRING = 'Select * [dbo].[Request]', @notcontain = ''
SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0
or CHARINDEX(@notcontain,syscomments.text)<>0))

SQL Queries for finding dependent objects

USE ReportServer

GO

/* Using the below query to finding the Finods_Replica DB dependent SSRS reports in royaltyopsreports DB*/

/*Seached with Finods_replica keyword to findout the SSRS reports, weather the finods_replica DB tables are directly refering to the reports*/

;WITH Reports

AS

(

       SELECT NAME AS ReportName,

                     CONVERT(NVARCHAR(MAX),

                     CONVERT(XML,CONVERT(VARBINARY(MAX),Content))) AS ReportContent

       FROM [Catalog] WHERE NAME IS NOT NULL

)

SELECT * FROM Reports WHERE ReportContent LIKE '%Test%' ORDER BY 1

/**********Finding Dependents in Stored Procedures*************/

USE 

SELECT Name,OBJECT_DEFINITION(OBJECT_ID) [Definition]

FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Test.%'

/**********Finding Dependents in Synonyms *************/

SELECT Name,base_object_name FROM sys.synonyms WHERE base_object_name LIKE '%Test%'

/**********Finding Dependents in Views *************/

SELECT Name,OBJECT_DEFINITION(OBJECT_ID) [Definition] FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Test%'

/**********Finding Dependents in SSIS Packages in MSDB(Integration Services)*************/

USE msdb

GO

;WITH SSISPackages

AS

(

SELECT [name] AS SSISPackageName

, CONVERT(NVARCHAR(MAX), CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata))) AS SSISPackageXML

FROM msdb.dbo.sysssispackages

)

SELECT * FROM SSISPackages WHERE SSISPackageXML LIKE '%Test%' ORDER BY 1

Table Partition schema query

select object_schema_name(i.object_id) as [schema],

    object_name(i.object_id) as [object],

    i.name as [index],

    s.name as [partition_scheme]

    from sys.indexes i

    join sys.partition_schemes s on i.data_space_id = s.data_space_id

       order by object

SQL Database table partitioning

--Add File groups
  ALTER DATABASE Db_Partition

ADD FILEGROUP January




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP February




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP March




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP April




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP May




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP June




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP July




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP Avgust




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP September




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP October




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP November




GO
  ALTER DATABASE Db_Partition

ADD FILEGROUP December




GO
  



--get file group's List
  
SELECT name AS AvailableFilegroups

FROM sys.filegroups

WHERE type = 'FG'


--Add Files Script
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartJan],

FILENAME = 'E:\Test\DATA\PartitioningDB.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [January]



　
 ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartFebruary],

FILENAME = 'E:\Test\DATA\PartitioningDB_February.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [February]




GO
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartMarch],

FILENAME = 'E:\Test\DATA\PartitioningDB_March.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [March]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartApril],

FILENAME = 'E:\Test\DATA\PartitioningDB_April.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [April]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartMay],

FILENAME = 'E:\Test\DATA\PartitioningDB_May.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [May]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartJune],

FILENAME = 'E:\Test\DATA\PartitioningDB_June.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [June]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartJuly],

FILENAME = 'E:\Test\DATA\PartitioningDB_July.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [July]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartAvgust],

FILENAME = 'E:\Test\DATA\PartitioningDB_Avgust.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [Avgust]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartSeptember],

FILENAME = 'E:\Test\DATA\PartitioningDB_September.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [September]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartOctober],

FILENAME = 'E:\Test\DATA\PartitioningDB_October.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [October]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartNovember],

FILENAME = 'E:\Test\DATA\PartitioningDB_November.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [November]




Go
  ALTER DATABASE [Db_Partition]

ADD FILE

(

NAME = [PartDecember],

FILENAME = 'E:\Test\DATA\PartitioningDB_December.ndf',

SIZE = 3072 KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024 KB

) TO FILEGROUP [December]



　
 --To check files created added to the filegroups run the following query:
   SELECT

name as [FileName],

physical_name as [FilePath]

FROM sys.database_files

where type_desc = 'ROWS'




GO
  



--CREATE PARTITION FUNCTION
  CREATE PARTITION FUNCTION [PartitioningByMonth] (datetime)

AS RANGE RIGHT FOR VALUES ('20140201', '20140301', '20140401',

'20140501', '20140601', '20140701', '20140801',

'20140901', '20141001', '20141101', '20141201');




--CREATE PARTITION SCHEME
  CREATE PARTITION SCHEME PartitionBymonth

AS PARTITION PartitioningBymonth

TO (January, February, March,

April, May, June, July,

Avgust, September, October,

November, December);




--Now we’re going to create the table using the PartitionBymonth partition scheme, and fill it with the test data:
  CREATE TABLE Reports

(ReportDate datetime PRIMARY KEY,

MonthlyReport varchar(max))

ON PartitionBymonth (ReportDate);




GO
  
 INSERT INTO Reports (ReportDate,MonthlyReport)

SELECT '20160105', 'ReportJanuary' UNION ALL

SELECT '20140205', 'ReportFebryary' UNION ALL

SELECT '20140308', 'ReportMarch' UNION ALL

SELECT '20140409', 'ReportApril' UNION ALL

SELECT '20140509', 'ReportMay' UNION ALL

SELECT '20140609', 'ReportJune' UNION ALL

SELECT '20140709', 'ReportJuly' UNION ALL

SELECT '20140809', 'ReportAugust' UNION ALL

SELECT '20140909', 'ReportSeptember' UNION ALL

SELECT '20141009', 'ReportOctober' UNION ALL

SELECT '20141109', 'ReportNovember' UNION ALL

SELECT '20141209', 'ReportDecember'

Select * from Reports



　
 ---
  
SELECT

p.partition_number AS PartitionNumber,

f.name AS PartitionFilegroup,

p.rows AS NumberOfRows

FROM sys.partitions p

JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id

JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id

WHERE OBJECT_NAME(OBJECT_ID) = 'Reports'