Archive

Archive for the ‘SQL Server’ Category

How To See Last Modified Date of Objects in SQL Server 2008

declare @day as int
set @day = 12
select * from
(SELECT [name],create_date,modify_date,’table’ type1,1 order1 FROM sys.tables
union
SELECT [name],create_date,modify_date,’view’ type1,2 order1 FROM sys.views
union
SELECT [name],create_date,modify_date,’trigger’ type1,5 order1 FROM sys.triggers
union
SELECT [name],create_date,modify_date,’sp’ type1,3 order1 FROM sys.procedures
WHERE [type] = ‘P’ AND is_ms_shipped = 0 AND [name] NOT LIKE ‘sp[_]%diagram%’
union
select [name],create_date,modify_date,’fn’ type1,4 order1 from sys.objects where type_desc like ‘%function%’)
as modify_table
where datediff(dd,modify_date,getdate())<@day
ORDER BY order1,modify_date DESC

Advertisements
Categories: SQL Server

Generating Random Strings of Characters in SQL

December 30, 2010 2 comments

The following Transact SQL procedure can be used to generate a random string of characters. As such it can be used to for example generate a default password for a user. The specific characters that are used to generate the string can be specified, so it can be customised (e.g. to only create passwords of digits or lower cased letters). The length of the generated random string can also be specified.

It is recommended that this SQL procedure be used as a stored procedure.

Using as a Stored Procedure

The following stored procedure creates a random string of characters of a length specified by the parameter @Length:

CREATE PROCEDURE sp_GeneratePassword
(
@Length int
)
AS
DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)

SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$'

DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''
SET NOCOUNT ON
SET @counter = 1

WHILE @counter < (@Length + 1)
BEGIN
SET @RandomNumber = Rand()
SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))
SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
SET @counter = @counter + 1

SET @RandomID = @RandomID + @CurrentCharacter
END

SELECT @RandomID AS 'Password'
GO
Categories: SQL Server Tags:

Automatic SQL Server Backup

October 13, 2009 Leave a comment

Automatic SQL Server Backup

I am running Windows Server 2008 with MS SQL Server and put togther this HOW-TO about how to backup a database automatically. I figured I would share it with you:

I am using Microsoft SQL Server 2008 Express but you should be able to use these instructions for all versions of Microsoft SQL Server 2005 and Microsoft SQL Server 2008.

CREATE A SQL FILE and save it as “backup.sql”

declare @filepath varchar(255)
declare @filename varchar(255)
declare @file varchar(510)

set @filepath=’D:\Praticle Test\’
declare @date varchar(8)
set @date=CONVERT(varchar(8), GETDATE(), 112)
set @filename= ‘-Backup.bak’
set @file=@filepath+ @date + @filename

Backup Database EHSolution To Disk = @file

CREATE A BATCH FILE With following content

sqlcmd -E -S pserver -i D:\backup.sql

SET BATCH FILE TO RUN IN WINDOWS TASK SCHEDULER

Start -> Run.

CODE

schtasks /create /sc Daily /st 03:30:00 /tn “MyTask” /tr “cmd /c D:\Backup.bat”


Categories: SQL Server

Use APPLY

Use APPLY
The apply statement was created for the situation where you put multiple inline nested queries in the one statement. For example, take the following statement:
SELECT soh.SalesOrderID,
Quantity=(SELECT TOP 1 (Quantity)
FROM Sales.SalesOrderDetails
WHERE  SalesOrderID = soh.SalesOrderID),
UnitPrice=(SELECT TOP 1 (UnitPrice)
FROM Sales.SalesOrderDetails
WHERE  SalesOrderID = soh.SalesOrderID)
FROM Sales.SalesOrderHeader soh
This performs an extra query, retrieving data from another table using the same criterion. This can now be replaced with the following:

SELECT soh.SalesOrderID, soh.OrderDate, a.*
FROM Sales.SalesOrderHeader soh
CROSS APPLY (
SELECT TOP (1) sod.UnitPrice, sod.Quantity
FROM Sales.SalesOrderDetail sod
WHERE sod.SalesOrderId = soh.SalesOrderId
ORDER BY sod.Quantity DESC
) as a

Categories: SQL Server Tags:

Making Store Procedure Generic

Making Store Procedure Generic

Consider creating stored procedures in the master database.

Use the special “sp_” prefix for these procedures, and use the undocumented system stored procedure “sp_MS_marksystemobject” to mark the stored procedure as a system stored procedure.

System stored procedures, although residing in the master database, operate on the database objects from where the stored procedure is executed.

All the usual caveats about using undocumented procedures and adding stored procedures to a system database apply.

Example:
***********
CREATE PROCEDURE sp_counts AS

DECLARE @TABLENAME VARCHAR(50)
DECLARE @SQL VARCHAR(200)
create table #temp
(
tablename varchar(300),
noofrows int
)

DECLARE C1 CURSOR FOR
select table_name from information_schema.tables where table_type=’base table’

OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME
WHILE @@FETCH_STATUS=0
BEGIN

SET @SQL = ‘select isnull(null,”’+@TABLENAME+”’),rows from sysindexes where indid<2 and id = object_id(‘+ ”” + @TABLENAME +”” +’)’

insert into #temp exec(@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME
END
close C1
deallocate C1
delete from #temp where tablename = ‘dtproperties’
select * from #temp order by noofrows desc
drop table #temp
GO

********
exec sp_MS_marksystemobject ‘sp_counts’
********

Now from any database you can give EXEC SP_COUNTS which gives you the tablename and total rows in the table.