Welcome to ChaudharyTechBlog

Declare @t table(Id INT identity(1,1), tName nvarchar(max), cName nvarchar(max)) Declare @t1 table(ID INT,tName nvarchar(max), cCount BIGINT) Declare @SQry nVarchar(MAX) = '', @tName nvarchar(max), @cName nvarchar(max) Declare @nCount int,@sCount int=1 Insert into @t(tName,cName) select a.name,b.name from sys.tables a inner join sys.all_columns b on a.object_id = b.object_id where b.is_identity = 1 select @nCount = COUNT(*) from @t While (@sCount <= @nCount) BEGIN select @tName = tname,@cName = cName from @t where id = @sCount --print N'select max('+ @cName +') nCount, '''+ @tName +''' Tname from '+ @tName +' having IDENT_CURRENT( '''+ @tName +''') <> max('+ @cName +')' Insert into @t1(ID,cCount, tName) exec (N'select '+ @sCount +', max('+ @cName +') nCount, '''+ @tName +''' Tname from '+ @tName +' having IDENT_C...

SELECT OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,SCHEMA_NAME(t.schema_id) AS SchemaName ,t.name AS TypeName ,c.max_length FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id ORDER BY c.OBJECT_ID;

stringVar array HundredHindiDigitArray := ["एक", "दो", "तीन", "चार", "पाँच", "छह", "सात", "आठ", "नौ", "दस","ग्यारह", "बारह", "तेरह", "चौदह", "पन्द्रह", "सोलह", "सत्रह", "अठारह", "उन्नीस", "बीस",     "इक्कीस", "बाईस", "तेईस", "चौबीस", "पच्चीस", "छब्बीस", "सत्ताईस", "अट्ठाईस", "उनतीस", "तीस", "इकतीस", "बत्तीस", "तैंतीस", "चौंतीस", "पैंतीस", "छत्तीस", "सैंतीस", "अड़तीस", "उनतालीस", "चालीस",     "इकतालीस", "बयालीस", "तैंतालीस", "चौवालीस", "पैंतालीस", "छियालीस", "सैंतालीस", "अड़तालीस", "उनचास", "पचा...

C ursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row. Life Cycle of Cursor Declare Cursor A cursor is declared by defining the SQL statement that returns a result set. Open A Cursor is opened and populated by executing the SQL statement defined by the cursor. Fetch When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation. Close After data manipulation, we should close the cursor explicitly. Deallocate Finally, we need to delete the cursor definition and released all the system resources associated with the cursor. Syntax to Declare Cursor Declare Cursor SQL Comaand is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The b...

EXEC sp_configure 'show advanced options' , 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries' , 1 RECONFIGURE GO

Whether you're bulk loading data or connecting to an OLE DB data source, OPENROWSET is a handy tool for retrieving data. Find out how to use the OPENROWSET function for SQL Server and Microsoft Access. There may be times when you'll want to run an ad hoc query that retrieves data from a remote OLE DB data source... or bulk loads data into a SQL Server table. In such cases, you can use the OPENROWSET function in Transact-SQL to pass a connection string and query to the data source in order to retrieve the necessary data. You can use the OPENROWSET function to retrieve data from any data sources that support a registered OLD DB provider, such as a remote instance of SQL Server or Microsoft Access. If you're using OPENROWSET to retrieve data from a SQL Server instance, that instance must be configured to permit ad hoc distributed queries. To configure the remote instance of SQL Server to support ad hoc queries, use the  sp_configure system stored procedure to ...

One of the blog followers asked this question “The code written by my colleague has WHERE 1=1. What does it mean?” Well. You have seen people using it when they dynamically construct WHERE condition based on the input values. Let us create this dataset CREATE TABLE #t (emp_name VARCHAR(100), experience INT, specialist VARCHAR(100)) INSERT INTO #t SELECT 'Pinal',12,'SQL' UNION ALL SELECT 'John',10,'JAVA' UNION ALL SELECT 'Sudhan',3,'SQL' Suppose you want to use two parameters and pass values for them in such a way that if the value is null, you need to skip the comparison Check the following code DECLARE @experience INT, @specialist VARCHAR(100), @sql VARCHAR(100) SET @sql=' where 1=1' SET @sql = @sql+CASE WHEN @experience IS NULL THEN '' ELSE ' and experience='''+CAST(@experience AS VARCHAR(100))+'''' END SET @sql = @sql+CASE WHEN @specialist IS NULL THEN '' ELSE ...