SQL Scripts

Some useful SQL scripts I've picked up over the years:

Find Server NameSELECT @@SERVERNAME


Change Server Name
sp_dropserver ''
go
sp_addserver '', 'local'
go
then Restart SQL Server Service


Cursor LoopsDECLARE curName CURSOR FOR SELECT *
OPEN curName
FETCH NEXT FROM curName INTO @Param
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH
NEXT FROM curName INTO @Param
END
CLOSE curName
DEALLOCATE
curName


Counter Loop DECLARE @CNT INT
SET @CNT = 1
WHILE(@CNT <>

-- LOOP CODE

SET @CNT = @CNT + 1
END


Resetting identity values
Use
TRUNCATE TABLE
ordbcc checkident (YourTable,reseed,1)


Strip time from datetime
CONVERT(DATETIME, CONVERT(CHAR(10), DateTimeField, 101))


Server Version Information
SELECT
SERVERPROPERTY('productversion') Version,
SERVERPROPERTY('productlevel') ServicePack,
SERVERPROPERTY('edition') Edition,
SERVERPROPERTY('MachineName') MachineName,
SERVERPROPERTY('ServerName') ServerName,
SERVERPROPERTY('LicenseType') LicenseType,
SERVERPROPERTY('NumLicenses') NumLicenses

Version numbers are:
9.00.1399.06 - SQL Server 2005
8.00.2039 - SQL 2000 SP4
8.00.760 - SQL 2000 SP3/3a
8.00.534 - SQL 2000 SP2
8.00.760 - SQL 2000 SP1
8.00.194 - SQL 2000
7.00.1063 - SQL 7 SP4
7.00.961 - SQL 7 SP3/3a
7.00.842 - SQL 7 SP2
7.00.699 - SQL 7 SP1
7.00.623 - SQL 7


Table Row Counts
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC

Empty all tables
To use
sp_SYS_EmptyAllTables 'RESET', 1


create Procedure dbo.sp_SYS_EmptyAllTables (@InputString AS CHAR(10), @ResetIdentity Bit)
As
Begin
IF @InputString = 'RESET'
BEGIN
Declare @SQL VarChar(500)
Declare @TableName VarChar(255)
Declare @ConstraintName VarChar(500)
Declare curAllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name From Information_Schema.Table_Constraints Where Constraint_Type='FOREIGN KEY'
Open curAllForeignKeys
Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
While @@FETCH_STATUS=0
Begin
Set @SQL = 'ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ' + @ConstraintName
Execute(@SQL)
Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
End
Declare curAllTables Cursor For Select Table_Name From Information_Schema.Tables Where TABLE_TYPE='BASE TABLE'
Open curAllTables
Fetch Next From curAllTables INTO @TableName
While @@FETCH_STATUS=0
Begin
Set @SQL = 'DELETE FROM ' + @TableName
If @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),'TableHasIdentity')=1
Set @SQL = @SQL + '; DBCC CHECKIDENT(''' + @TableName + ''',RESEED,0)'
Execute(@SQL)
Fetch Next From curAllTables INTO @TableName
End
Fetch First From curAllForeignKeys INTO @TableName,@ConstraintName
While @@FETCH_STATUS=0
Begin
Set @SQL = 'ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ' + @ConstraintName
Execute(@SQL)
Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
End
Close curAllTables
Deallocate curAllTables
Close curAllForeignKeys
Deallocate curAllForeignKeys
end
else
begin
print 'Not run - RESET not passed in'
end
End