Zafar Iqbal

Today I had to add a new date column to one of my table, and populate it with some random entries. I found a script from SQL Team Blog about generating random numbers. I modified it a little and used it to populate these dates based on another date I had in my table.

Here is modified code:

-- Create the variables for the random number generation
DECLARE @Upper int;
DECLARE @Lower int-- This will create a random number between 1 and 365
SET @Lower = 1 -- The lowest random number
SET @Upper = 365 -- The highest random number

-- we'll use this temp table to assign a random number to each ID value
CREATE TABLE #temp (ID int NOT NULL, RandNum float NULL)
INSERT #temp (ID) SELECT ID FROM FOO

-- now, assign a new random value to each key value in #temp
DECLARE @id int
DECLARE Randomizer CURSOR
FOR SELECT RandNum FROM #temp

OPEN Randomizer
FETCH NEXT FROM Randomizer INTO @id

WHILE @@Fetch_Status != -1
BEGIN
UPDATE #temp SET RandNum = rand()
WHERE CURRENT OF Randomizer

FETCH NEXT FROM Randomizer
END

CLOSE Randomizer
DEALLOCATE Randomizer

UPDATE F
SET F.NewDate = dateadd(dd, round((T.RandNum * 100), 0), F.AnyDate)
FROM
FOO F INNER JOIN #temp T ON F.ID = T.ID

DROP TABLE #temp

Now say your table does not have an identify column, you don’t want to use a temporary table or alter your existing table, but you still would like to have a record number associated with each record. In this case you could use a self join to return a record number for each row. Here is an example that calculates a RecNum column, and displays the LastName for each record in the Northwind.dbo.Employees table. This example uses count(*) to count the number of records that are greater than or equal LastName in this self join.

SELECT COUNT(*) RecNum,
       a.LastName
FROM   Northwind.dbo.Employees a
       JOIN Northwind.dbo.Employees b
            ON  a.LastName >= b.LastName
GROUP BY
       a.LastName
ORDER BY
       a.LastName

The results from this query looks like this:

(Continue Reading …)

Here how you can convert string date to a datetime value:

DECLARE @DateString char(14)SET @DateString = '20060703074815'SELECT CAST(SUBSTRING(@DateString, 1, 4) + '-' +SUBSTRING(@DateString, 5, 2) + '-' +SUBSTRING(@DateString, 7, 2) + 'T' +SUBSTRING(@DateString, 9, 2) + ':' +SUBSTRING(@DateString, 11, 2) + ':' +SUBSTRING(@DateString, 13, 2) AS datetime)

If you want to find row count for each table in your database without naming any table then use following script:

exec sp_MSforeachtable 'select count(*) as nr_of_rows, ''?'' as table_name from ?'

When you need to find all procedures and function referencing a particular table then use following:

SELECT o.name, t.TABLE_NAME, c.textFROM syscomments c JOIN sysobjects o ON c.id = o.id JOIN INFORMATION_SCHEMA.Tables t ON c.text LIKE '%'+t.TABLE_NAME+'%'