Get First and Last Saturday of Each Month

declare @year int
set @year =2011
-- First and Last Sunday by SqlServerCurry.com
select min(dates) as first_saturday,max(dates) as last_saturday from
(select dateadd(day,number-1,DATEADD(year,@year-1900,0))
as dates from master..spt_values
where type='p' and number between 1 and
DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))
) as t where DATENAME(weekday,dates)='saturday'
group by DATEADD(month,datediff(month,0,dates),0)

Prevent Databases From Being Dropped

The best way to prevent users from dropping databases is to not grant the necessary access to drop a database, it can sometimes come in handy to have a catch-all to stop any databases from being dropped.  This trigger is currently very simple – if the trigger is active, then it prevents any database on the instance from being dropped.  You must disable or delete the trigger in order to drop a database.  A more advanced version could check the username or the hostname of the user trying to perform the delete, or could check a table that contains the names of the databases that can be dropped (you would then have to insert the name of your database to this table, and then drop the database).

CREATE DATABASE TestDB
GO

USE Master
GO

CREATE TRIGGER Trig_Prevent_Drop_Database ON ALL SERVER
FOR DROP_DATABASE
AS
    RAISERROR('Dropping of databases has been disabled on this server.', 16,1);
    ROLLBACK;
GO

DROP DATABASE TestDB -- Shouldn't work
GO 

-- Drop the trigger to allow deletions (you could also 
-- disable the trigger and then reenable it)
DROP TRIGGER Trig_Prevent_Drop_Database ON ALL SERVER
GO

DROP DATABASE TestDB -- Should work
GO

Move TempDB Data and Log file to some other location

Restart SQL Server with the command line parameter -T3608, and then run alter database commands to move the location of TempDB:

ALTER DATABASE TempDB MODIFY FILE  (name = tempdev, filename = 'D:\Data\tempdb.mdf')
GO

ALTER DATABASE TempDB MODIFY FILE (name = templog, filename = 'D:\Logs\templog.ldf')
GO

Restart SQL Server without -T3608, and TempDB should be created in the new location. 

The alternative is to not reconfigure SQL Server at all, but rename an existing disk to the missing drive letter, just to get SQL Server started.

check if all characters of a string are same

Consider the following example:


declare @t table(data VarChar(20))
insert into @t
select '3333333333' as data union all
select '55555436' union as data all
select 'xxxxxxxxxx'+CHAR(32) union as data all
select 'sddfgghfdhrsd' union as data all
select '0~~~~~~~~~~~~~' union as data all
select '555555555555' union as data all
select ']##########' union as data all
select ']    ' union as data all
select ']]]]]]]]]]]]]' as data


Here we see that only three values have all same characters. We can use many methods to find out this but the simplest method is:


select data from @t where PATINDEX('%[^'+left(replace(data,']',char(0)),1)+']%',replace(data,']',char(0)))=0


The result is
Data
--------------------
3333333333
555555555555
]]]]]]]]]]]]]


The logic is to see if there is a character which is different than the first character. If there is different character the patindex function will return a value greater than 0 otherwise all characters are same and it will return 0

Find Database Object information

Here is a script to find All Tables in a Database. It will also find No of Records, Indexes, No of Indexes, Space occupied by a Table.

SET NOCOUNT ON

CREATE TABLE #TableInfo (Name sysname NULL,
                         Rows int,
                         Reserved varchar(256) NULL,
                         Data varchar(256) NULL,
                         Index_Size varchar(256) NULL,
                         Unused varchar(256) NULL)
CREATE TABLE #DBTables (Instance sysname NULL,
                        DBName sysname NULL,
                        TableName sysname NULL,
                        TableType char(2),
                        TableRows int NULL,
                        IndexCount int NULL,
                        ReservedKB int NULL,
                        DataSizeKB int NULL,
                        IndexSizeKB int NULL,
                        UnusedKB int NULL)

DECLARE @DatabaseName varchar(64)
DECLARE @TableName varchar(256)
DECLARE @xtype char(2)
DECLARE @TableRows int
DECLARE @IndexCount int
DECLARE @ReservedKB int
DECLARE @DataSizeKB int
DECLARE @IndexSizeKB int
DECLARE @UnusedKB int 

DECLARE cs CURSOR FOR
  SELECT
    su.name + '.[' + so.name + ']',
    so.xtype
  FROM sysobjects so INNER JOIN sysusers su ON (so.uid = su.uid)
  WHERE so.xtype in ('U', 'S') 

SELECT @DatabaseName = DB_NAME(dbid)
FROM master..sysprocesses
WHERE spid=@@SPID

OPEN cs
FETCH NEXT FROM cs INTO @TableName, @xtype
WHILE (@@FETCH_STATUS = 0)
BEGIN
  TRUNCATE TABLE #TableInfo

  IF @xtype = 'U'
    INSERT INTO #TableInfo exec sp_spaceused @TableName , @updateusage = 'TRUE'
  ELSE
    INSERT INTO #TableInfo exec sp_spaceused @TableName 

  SELECT
    @TableRows = Rows,
    @ReservedKB = CAST(SUBSTRING(Reserved, 1, CHARINDEX('KB', Reserved, 1)-1) AS int),
    @DataSizeKB = CAST(SUBSTRING(Data, 1, CHARINDEX('KB', Data, 1)-1) AS int),
    @IndexSizeKB = CAST(SUBSTRING(Index_Size, 1, CHARINDEX('KB', Index_Size, 1)-1) AS int),
    @UnusedKB = CAST(SUBSTRING(Unused, 1, CHARINDEX('KB', Unused, 1)-1) AS int)
  FROM #TableInfo

  SELECT @IndexCount = COUNT(*)
  FROM sysindexes
  WHERE
    id=OBJECT_ID(@TableName) AND
    name NOT LIKE  '_WA_Sys%' AND
    indid > 0

  INSERT INTO #DBTables
  VALUES (@@SERVERNAME,
          @DatabaseName,
          @TableName,
          @xtype,
          @TableRows,
          @IndexCount,
          @ReservedKB,
          @DataSizeKB,
          @IndexSizeKB,
          @UnusedKB)

  FETCH NEXT FROM cs INTO @TableName, @xtype
END
CLOSE cs
DEALLOCATE cs

SELECT
  Instance,
  DBName,
  TableName,
  TableType,
  TableRows,
  IndexCount,
  ReservedKB,
  DataSizeKB,
  IndexSizeKB,
  UnusedKB
FROM #DBTables

DROP TABLE #DBTables
DROP TABLE #TableInfo