The Young DBA

Sometimes there would be a requirement to add serial number (or) row number to the result set. Example: The requirement would be to select some columns from the database tables along with Serial Number like below SNo Customer_Name Customer_Phone_Number 1 aaa xxx-xxx-xxxx 2 bbb xxx-xxx-xxxx So, in this post we will see how we can select that using T-SQL on Microsoft SQL Server 2008 We can achieve this in several ways: 1) Using  Row_Number ()   Row_Number() Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.  T-SQL Script:          SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID) AS [S.No]                         ,CUSTOMER_NAME                         ,CUSTOMER_PHONE_NUMBER         FROM database.dbo.tblTableName 2) Using  Identity (Property)   Identity() property creates an identity column in the table. So, first we need to cre

Searching Hard Coded Values from Entire Database-Objects is very Interesting Task for me. My last Blog ( Search Dynamic Values ) was for Dynamic Values from Tables . Today I want to show how I am searching Hard Coded values (E-mail Ids and Mobile numbers) from all  Procedures  or  Views  from our Database.We should avoid the Hard codes in objects. To manages Hard codes in objects  i was simply Converting all procedures and Views in script and after that using find -replace i was managing changes...It was very time consuming and bothering work form me... Below Query help and save my lot of time for learning extras..... declare @text varchar ( 100 ) declare @dbname varchar ( 100 )        set @text = 'sumank'   set @dbname = 'masterdatabase' if @dbname is null begin --enumerate all databases. DECLARE #db CURSOR FOR Select Name from master .. sysdatabases declare @c_dbname varchar ( 64 )    OPEN #db FETCH #d

Searching from ENTIRE DATABASE is the common requirement for every dba/developer in organization. My boss is always asking to me Suman Check this 958200000  MOBILE NUMBER or smnjha989@gmail.com   E-ID where is available and replace with this new number, he/she left the Position but still getting notification in mail or alert in mobile…  It was very time consuming task for me … But Now this is easy for me for finding. Just running the below query and after few minute gives the table name-column name and value where it contains………….. I am using below TSQL just try……….. DECLARE @SearchingText nvarchar ( 256 ) SET   @SearchingText = 'XXXXXXXXXXXXX' BEGIN       CREATE TABLE #Results ( ColumnName nvarchar ( 370 ), ColumnValue nvarchar ( 3630 ))       SET NOCOUNT ON       DECLARE @TableName nvarchar ( 256 ), @ColumnName nvarchar ( 128 ), @SearchStr2 nvarchar ( 110 )       SET   @TableName = ''       SET @SearchStr2 = QUOTE