Get the Sql Database tables record count till date

SELECT T.name AS [Table Name],

I.rows AS [Record Count]

FROM sys.tables AS T

INNER JOIN sys.sysindexes AS I

ON T.object_id = I.id

AND I.indid < 2

ORDER BY I.rows DESC




SELECT

t.NAME AS TableName,

i.name as indexName,

p.[Rows],

sum(a.total_pages) as TotalPages,

sum(a.used_pages) as UsedPages,

sum(a.data_pages) as DataPages,

(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

(sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

WHERE

t.NAME NOT LIKE 'dt%' AND

i.OBJECT_ID > 255 AND

i.index_id <= 1

GROUP BY

t.NAME, i.object_id, i.index_id, i.name, p.[Rows]

ORDER BY

p.[Rows] DESC

--object_name(i.object_id)

Loop through the SQL Table and add columns to it

create table #Temp

(
testID int

)
select object_id('tempdb..#Temp')

SELECT top 10 OBJECT_ID FROM tempdb.sys.columns WHERE object_id = object_id('#Temp')

select * from #Temp

declare @counter_val int = 0

WHILE ( SELECT COUNT(*) FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#Temp')) < 1000

BEGIN

set @counter_val = @counter_val + 1

DECLARE @SQL NVARCHAR(MAX)

declare @fieldname nvarchar(20)

SET @fieldname = 'niraj' + cast(@counter_val as nvarchar(10))

   

SET @SQL = 'ALTER TABLE #Temp

ADD [' + CAST(@fieldname AS VARCHAR(10)) + '] varchar(30)'

--PRINT @SQL

EXECUTE (@SQL)

END

　

　

drop table #Temp

How can I retrieve old code before merge with new code in TFS

Issue -
By mistake if you merge the new code(of server) with your local code and now you want to retrieve the old code before the merge ...

Solution -
You can use below path to get the temp file which has old code, TFS storing this data as temp data in this folder ... VERY MUCH USERFUL

Path : C:\Users\%USERNAME%\AppData\Local\Temp\TFSTemp\

Avoid "print" statement from Sql Store procedure, It impacts the performance

DECLARE @date DATETIME2

DECLARE @count INT

SET @count = 1

SET @date = SYSUTCDATETIME()

WHILE @count < 1000000





BEGIN


--RAISERROR ('%d',0,1, @count) WITH NOWAIT


--PRINT @count

SET @count = @count + 1




END


SELECT DATEDIFF(MICROSECOND, @date, SYSUTCDATETIME()) / 1000000.

HMACSHA256 based API call using WebRequest

private void button1_Click(object sender, EventArgs e)
       {
           try
           {
               WebRequest request = WebRequest.Create("DemoAPI");
               request.ContentType = "application/json; charset=utf-8";
               string authInfo = Signature("test", "Keys1", "keys2");
               request.Headers["Authentication"] = authInfo;
               request.Method = "POST";
               request.ContentLength = 0;
               var response = request.GetResponse();
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }
 
       public string Signature(string secret, string value, string key2)
       {
           var secretBytes = Encoding.UTF8.GetBytes(secret);
           var valueBytes = Encoding.UTF8.GetBytes(value);
           string signature;
 
           using (var hmac = new HMACSHA256(secretBytes))
           {
               var hash = hmac.ComputeHash(valueBytes);
               signature = Convert.ToBase64String(hash);
           }
           //return signature;
           return key2 + ":" + signature;
       }

SQL Server : List of unused sql tables from database With RowCount 0 or not used more that 2 months

; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate )

AS (

SELECT DBTable.name AS TableName

,PS.row_count AS TotalRowCount

,DBTable.create_date AS CreatedDate

,DBTable.modify_date AS LastModifiedDate

FROM sys.all_objects DBTable

JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name

WHERE DBTable.type ='U'

AND NOT EXISTS (SELECT OBJECT_ID

FROM sys.dm_db_index_usage_stats

WHERE OBJECT_ID = DBTable.object_id )




)


-- Select data from the CTE


SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate

FROM UnUsedTables

ORDER BY TotalRowCount ASC

SQL Server database level Store procedure to check dependent records

sp to check dependent records
CREATE PROCEDURE [dbo].[sp_IsDependentRecordExist]
 @tableName varchar(250),
 @idValue varchar(50)

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    declare @tableCount as int
 declare @RowID as int
 declare @recordExist as bit
 Declare @tbl varchar(200)
 Declare @col as varchar(200)
 declare @refCount as int
 set @recordExist = 0
 --create temp table for dependent table
 IF OBJECT_ID('tempdb..#dependentTables') IS NOT NULL
 BEGIN
    DROP TABLE #dependentTables
 END
 CREATE TABLE #dependentTables
  (tableid int,Parent_Table varchar(250),Parent_Column varchar(100),Reference_Table varchar(250),Reference_Column varchar(100)) 
 -- INSERT data INTO #dependentTables
 INSERT INTO #dependentTables
  (tableid,Parent_Table,Parent_Column,Reference_Table,Reference_Column)
 SELECT
  row_number() over (order by (select NULL)) as tableid,
  SO_P.name as Parent_Table
  ,SC_P.name as Parent_Column
  ,SO_R.name as Reference_Table
  ,SC_R.name as Reference_Column
 from sys.foreign_key_columns FKC
  inner join sys.objects SO_P on SO_P.object_id = FKC.parent_object_id
  inner join sys.columns SC_P on (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id)
  inner join sys.objects SO_R on SO_R.object_id = FKC.referenced_object_id
  inner join sys.columns SC_R on (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id)
  where
   ((SO_R.name = @tableName) AND (SO_R.type = 'U'))

 SELECT @tableCount = COUNT(1) from #dependentTables
 SET @RowID = (SELECT TOP 1 tableid from #dependentTables order by tableid)
 --loop through dependent Tables
 WHILE (@tableCount > 0)
 BEGIN
 
  SELECT @tbl =Parent_Table from #dependentTables where tableid=@RowID
  SELECT @col=  Parent_Column from #dependentTables where tableid=@RowID
  IF OBJECT_ID('tempdb..#Data') IS NOT NULL
  BEGIN
     DROP TABLE #Data
  END
  CREATE TABLE #Data (var int)
 
  if ((select count(1) from sys.columns where Name = N'IsDeleted' and Object_ID = Object_ID(@tbl)) > 0)
  begin
   INSERT #Data exec ('Select count(1) from ' + @tbl + ' where '+  @col +'='+ @idValue + ' and IsDeleted=0')
  end
  else
  begin
   INSERT #Data exec ('Select count(1) from ' + @tbl + ' where '+  @col +'='+ @idValue)
  end
   
  SELECT @refCount = var from #Data
 
  IF OBJECT_ID('tempdb..#Data') IS NOT NULL
  BEGIN
     DROP TABLE #Data
  END
  if @refCount>0
  begin
   set @recordExist = 1
   break
  end
  SET @RowID = @RowID + 1
  set @tableCount = @tableCount - 1
 END
 --drop temp tables
 IF OBJECT_ID('tempdb..#dependentTables') IS NOT NULL
 BEGIN
    DROP TABLE #dependentTables
 END

 select @recordExist as IsDependentRecordExist

END