Joseph Michael Pesch
VP Programming

SQL Server Query to Gather Individual Table Space Used

by 22. October 2012 16:29

 

select

  tablename = o.name

, totalpages = sum(a.total_pages)

, usedpages = sum(a.used_pages)

, pages = sum(case when a.type <> 1 then a.used_pages

when p.index_id < 2 then a.data_pages else 0 end)

, SUM(a.used_pages)*8096 AS 'Size(B)'

, rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)

from sys.objects o

join sys.partitions p on p.object_id = o.object_id

join sys.allocation_units a on p.partition_id = a.container_id

where o.type = 'U'

group by o.name

order by usedpages desc

 

Tags:

SQL Server

SQL DB Table Record History Template

by 12. September 2012 10:21

Here is a template script to create a standard set of objects to track changes to table data.

/*
  drop trigger SchemaName.TableName_IUX
  drop table SchemaName.TableName
  drop view SchemaName.vxTableName
  drop table SchemaName.TableNameHistory
  drop view SchemaName.vxTableNameHistory
*/

/*
  drop table SchemaName.TableName
*/
create table SchemaName.TableName
(
  /* single record per modification instance */
    Timestamp timestamp not null
  , TableNameId uniqueidentifier not null
  , LastUpdateDate datetime not null default getdate()
  , LastUpdateByUserId uniqueidentifier not null
  /* add columns here */
)
go
alter table SchemaName.TableName
add constraint
  PK_SchemaName_TableName primary key clustered
  (
    TableNameId
  ) with ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
on [PRIMARY]
go
/*
  drop view SchemaName.vxTableName
*/
create view SchemaName.vxTableName
as
select
    TableNameId
  , LastUpdateDate
  , LastUpdateByUserId
  /* add columns here */
from 
  SchemaName.TableName
go
/*
  drop table SchemaName.TableNameHistory
*/
create table SchemaName.TableNameHistory
(
  /* single record per modification instance */
    HistoryId uniqueidentifier not null default newid()
  , TableNameId uniqueidentifier not null
  , LastUpdateDate datetime not null default getdate()
  , LastUpdateByUserId uniqueidentifier not null
  /* add columns here */
)
go
alter table SchemaName.TableNameHistory
add constraint
  PK_SchemaName_TableNameHistory primary key clustered
  (
    HistoryID
  ) with ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
on [PRIMARY]
go
/*
  drop view SchemaName.vxTableNameHistory
*/
create view SchemaName.vxTableNameHistory
as
select
    TableNameId
  , LastUpdateDate
  , LastUpdateByUserId
  /* add columns here */
from 
  SchemaName.TableNameHistory
go
/*
  drop trigger SchemaName.TableName_IUX
*/
create trigger SchemaName.TableName_IUX on SchemaName.TableName for insert, update
as begin
  insert into SchemaName.vxTableNameHistory
    select t1.* 
    from SchemaName.vxTableName t1
    inner join inserted t2 on t1.TableNameID = t2.TableNameID
end
go

Tags:

SQL Server

Change Owner of SSRS Subscription on SQL Server 2008 R2

by 9. April 2012 10:26

Recently a DBA left the company and before his final day we disabled his login account to make sure that nothing broke (i.e. no jobs were running under his account, etc.).  The next couple days everything seemed fine.  Then after he left we actually deleted his account.  After deleting his account every SSRS report subscription that was originally created by him stopped working.  Some of them gave error messages that indicated some type of problem with the user account while others had very vague error messages.  The bottom line is that they ran fine when the AD account existed (regarless of the state of the account); but, the fail once the account is removed.  The initial fix was simply to create a new version of the subscription under a different user account; however, there were so many that this would be a very tedious and time consuming process. 

As an alternative the attached script can be used to change the subscription owner of all subscriptions belonging to a specific user. To execute the Script, please follow these steps:

1. Open Command line and Change Directory to this location “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn”
2. Execute the following command:

  • rs -i <Path to ChangeSubscriptionOwner.rss Script> -s <ReportServer URL> -e Mgmt2010 -v varOwner="<CurrentSubscriptionOwner>" -v varNewOwner="<NewSubscriptionOwner>"

Example:

  • rs -i D:\ChangeSubscriptionOwner.rss -s http://myserver/reportserver -e Mgmt2010 -v varOwner="mydomain\origuserid" -v varNewOwner="mydomain\newuserid"

This will change all the Subscriptions owned by "mydomain\originaluserid" to "mydomain\newuserid".

Note: Please Execute this script on the Reporting Services Server.

Note 2: This script and should be used only when Good ReportServer and ReportServerTempDB backups are available. As a good practice, you should also take a backup of the Reporting Services EncryptionKey.

Tags:

SQL Server | SSRS

Microsoft SQL Server Halt Script Execution On Error

by 14. February 2012 09:41

I have two basic styles of scripting to perform a halt during a Microsoft SQL Server TSQL script execution through SQL Management Studio.  The first is a soft halt where I will throw an exception message for the user to read and take action of (i.e. manually cancel the script execution at that point if necessary).  It will wait with a predefined arbitrary time delay and if the user takes no action within the time limit the script will continue.  The second is a hard halt where we do not leave it up to the user.  For the hard halt the script must be run in SQLCMD mode.  The second style involves one of two possible methods.  First would be simply setting the severity level of the raiserror statment to level 20.  However, this is only allowed if the user running the script is a member of sysadmin role.  So, the fallback to that (i.e. if the user is not a member of the sysadmin role) is to first check to see if the user is running the script in SQLCMD mode, and, if they are not throw an error message telling them that they must run the script in SQLCMD mode or as a user who is member of sysadmin role.  Immediately after that set a maximum waitfor delay (24 hours is the max delay).  NOTE: The fallback script will still execute if the user leaves the connection open and running for the full 24 hour delay so this is still tecnically not a hard stop in all cases.

Style 1 - Soft Halt

declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! '
  + ' Make sure you are running this script on the desired server. '
  + ' If this is not the desired server please cancel the script execution now! '
  + ' Otherwise, this script will execute in 30 seconds. '
  + ' The current server is: ' + @@servername
select @errMsg
print @errMsg
raiserror(@errMsg, 16, 1) with nowait
-- wait for the user to read the message, and terminate the script manually if need be
waitfor delay '00:00:30'; 

Style 2 - Hard Halt

:setvar SqlCmdMode "DbNameHere"
go
declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! This script MUST be run in SQLCMD mode!  Please cancel script now and re-run in SQLCMD mode.'
if ('$(SqlCmdMode)' = '$' + '(SqlCmdMode)')     
if is_srvrolemember('sysadmins') = 1
  raiserror(@errMsg, 20, -1) with log 
else begin
    select @errMsg
    print @errMsg
    raiserror(@errMsg, 16, 1) with nowait
    -- wait for the user to read the message, and terminate the script.
    waitfor delay '23:59:59'; 
end
go

Tags:

SQL Server

SQL Server Management Studio "Invalid Object Name ..." when Object Does Exist

by 25. January 2012 10:06

I was struggling to understand why the SSMS intellisense was highlighting certain objects in my query window and indicating that they were not valid; when in fact, they did exist.  I tried closing and re-opening the studio, refreshing the local cache (Edit -> IntelliSense -> Refresh Local Cache (CRTL+Shift+R)).  All to no avail.  I finally realized the issue was due to the fact that the top of my SQL script had statements to drop and recreate some of the objects dynamically (e.g. if exists(...) drop XXX go select * into XXX from YYY).  It turns out that even though the objects actively existed in the database the fact that the script had a conditional drop statement of the objects, the IntelliSense seemed to override reality with fiction (i.e. thinking they are or will be dropped, perhaps?).  Anyway, if I simply commented out the drop statements all the errors went away.  Strange...

Tags:

SQL Server

SQL Server Get First and Last Day's of Previous, Current and Next Month

by 10. January 2012 09:55

 

select

  [First Day of Previous Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate())-1)

  , dateadd(mm, -1, getdate()))

  , 101)

, [Last Day of Previous Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate()))

  , getdate())

  ,101)

, [First Day of Current Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate())-1)

  , getdate())

  , 101)

, [Last Day of Current Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate())))

  , dateadd(mm, 1, getdate()))

  , 101)

, [First Day of Next Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate()))-1)

  , dateadd(mm, 1, getdate()))

  , 101)

, [Last Day of Next Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate())))

  , dateadd(mm, 2, getdate()))

  , 101)

 

Tags:

SQL Server

SQL Date Manipulation

by 25. October 2011 09:35

Here is a query to get the previous quater:

select
  dateadd(qq, datediff(qq, 0, getdate())-1, 0) 
, dateadd(ms, -3, dateadd(qq, datediff(qq, 0, getdate()), 0))

Tags:

SQL Server

SQL Out of Log Space

by 3. October 2011 15:39

To recover allocated but unused space run this command:

dbcc shrinkdatabase ('DBNAME_HERE', TRUNCATEONLY)

Tags:

SQL Server

SQL Script to Generate Table Inserts

by 23. September 2011 07:46

This script will accept a database name and comma delimited list of table names.  It will generate insert statements for all the records in the desired tables.  I will be adapting the script to include schema as well as table name.  The original script was provided by: http://blog.boxedbits.com/archives/50

CREATE Procedure [dbo].[sp_CreateDataLoadScript]
@databaseName	varchar(128) ,
@TblNames varchar(max)
as begin

	set nocount on;

	create table #a (id int identity (1,1), ColType int, ColName varchar(128))
	create table #out (lnr int, statements varchar(max))
	declare @sql nvarchar(4000)
	declare @TblName as varchar(128)
	declare @idx as bigint
	declare @previdx as bigint
	declare @last as bit
	declare @hasIdentity as bit
	declare @lnr as int

	set @lnr=0

	set @idx=charindex(',',@TblNames)
	set @previdx=1

	if @idx>0 begin  /* many tables */
		set @TblName=ltrim(rtrim(substring(@TblNames,@previdx,@idx-@previdx)))
		set @previdx=@idx+1
		set @last=0
	end
	else begin /* 1 table */
		set @TblName=ltrim(rtrim(@TblNames))
		set @last=1
	end

	while len(@TblName)>0 begin
	

		select @sql = 'select case when DATA_TYPE like ''%char%'' or DATA_TYPE like ''%date%'' or DATA_TYPE like ''uniqueidentifier'' then 1 else 0 end , COLUMN_NAME
			from 	information_schema.columns
			where 	TABLE_NAME = ''' + @TblName + '''
			order by ORDINAL_POSITION
			'

		select 	@sql = 'exec ' +  @databaseName + '.dbo.sp_executesql N''' + replace(@sql, '''', '''''') + ''''
		
		insert 	#a (ColType, ColName)
		exec (@sql)
		

		select @hasIdentity=max(cast(clmns.is_identity as int))
		from sys.tables AS tbl
		INNER JOIN sys.all_columns AS clmns ON  clmns.object_id = tbl.object_id
		where 	tbl.name = @TblName

		declare	@id int ,
		@maxid int ,
		@cmd1 varchar(7000) ,
		@cmd2 varchar(7000)

		insert into #out select @lnr, '/* ' + @TblName + ' */'
		set @lnr = @lnr+1
		insert into #out select @lnr, 'truncate table ' + @TblName
		set @lnr = @lnr+1

		if @hasIdentity=1 begin
			insert into #out select @lnr, 'set identity_insert ' + @TblName + ' ON'
			set @lnr = @lnr+1
		end
			
		select 	@id = 0 , @maxid = max(id)
		from 	#a

		select	@cmd1 = 'insert into #out select ' + cast(@lnr as varchar) + ', '' insert ' + @TblName + ' ( '
		select	@cmd2 = ' + '' select '' + '
		while @id < @maxid
		begin
			select @id = min(id) from #a where id > @id

			select 	@cmd1 = @cmd1 + '[' + ColName + '],'
			from	#a
			where	id = @id

			select @cmd2 = 	@cmd2
					+ ' case when [' + ColName + '] is null '
					+	' then ''null'' '
					+	' else '
					+	  case when ColType = 1 then  ''''''''' + replace(convert(varchar(max),[' + ColName + ']),'''''''','''''''''''') + ''''''''' else 'convert(varchar(50),[' + ColName + '])' end
					+ ' end + '','' + '
			from	#a
			where	id = @id
		end

		select @cmd1 = left(@cmd1,len(@cmd1)-1) + ' ) '' '
		select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @databaseName + '.dbo.' + @tblName

		exec (@cmd1 + @cmd2)
		truncate table #a

		if @hasIdentity=1 begin
			insert into #out select @lnr, 'set identity_insert ' + @TblName + ' OFF'
			set @lnr = @lnr+1
		end

		insert into #out select @lnr, 'go'
		set @lnr = @lnr+1

		-- next table
		set @idx=charindex(',',@TblNames,@previdx)
		if @idx>0 begin  /* more tables */
			set @TblName=ltrim(rtrim(substring(@TblNames,@previdx,@idx-@previdx)))
			set @previdx=@idx+1
		end
		else if @last=0 begin /* one more */
			set @TblName=ltrim(rtrim(substring(@TblNames,@previdx,8000)))
			set @last=1
		end
		else  /* done */
			set @TblName=''
	end

	drop table #a

	print 'use ' + @databaseName
	print 'go'
	print 'set nocount on'


	declare @statement varchar(max)
	declare @o int
	declare c_out cursor  LOCAL FAST_FORWARD for select statements from #out order by lnr
	open c_out

	declare @i int
	set @i=0
	fetch next from c_out into @statement

	while @@fetch_status=0 begin
		set @i=@i+1
		if @i=1000 begin
			print 'go'
			set @i=0
		end

		print @statement

		fetch next from c_out into @statement
	end

	close c_out
	deallocate c_out
	drop table #out
end

Tags:

SQL Server

Forgot SQL Server Login

by 27. June 2011 08:52
On my current computer I had installed SQL Server 2008 locally some time back and forgot the "sa" password I had assigned at the time of installation and could not find it documented anywhere.  Also, it seems I did not add my windows account as an administrator as I could not login with integrated security even using the windows account that has local administrator access on the machine.  To solve this problem, I stopped the SQL service, added the "single user mode" parameter to the startup parameters, restarted the service, and changed the "sa" password using a SQL query window (SQL command shown below).
To add the "single user mode" startup parameter, open the "Sql Server Configuration Manager", right click on the "SQL Server (INSTANCENAMEHERE)" service and select "Properties", then click on the "Advanced" tab, scroll down to the "Startup Parameters" and add "-m;" (without the quotes) to the front of the existing parameters.
Restart the service, then through Sql Management Studio, open a new query window and change the connection string to your Sql Instance and run the following commands.  Note: You can put whatever new password you want in the @new parameter.  Once that is finished, go back and remove the "-m;" from the startup parameters, restart the service and login with your "sa" account and the new password you assigned.
exec sp_password @new='NewPasswordHere', @loginame='sa' 
go 
alter login sa 
enable 
go
Alternatively, if you cannot even login to the server, perform the following:
  1. Start the instance of SQL Server in single-user mode by using either the -m; or -f; options.
  2. Start command prompt as local administrator
  3. Connect to the server: sqlcmd -E -S SERVER_NAME_HERE
  4. Add yourself (computername\username) to the sysadmin SQL group with the following commands:
    • create login [computername\username] from windows;
    •  exec sp_addsrvrolemember 'computername\username', 'sysadmin';
  5. Restart the server in normal mode

Tags:

SQL Server

ExecuteScalar() Truncates Xml Using SQL For Xml

by 24. February 2011 07:51

Use this style as the Sql server actually returns multiple rows of the Xml in 2,033 byte chunks:

System.Xml.XmlReader rdr = cmd.ExecuteXmlReader();
rdr.Read();
while (rdr.ReadState != System.Xml.ReadState.EndOfFile)
  xml.Append(rdr.ReadOuterXml());

Tags:

ASP.Net | SQL Server

SQL Database Summary Query

by 23. February 2011 15:03

SELECT

  vfs.database_id,

  DBName = DB_NAME(vfs.database_id),

  DB_Reads = SUM(vfs.num_of_reads),

  DB_Writes = SUM(vfs.num_of_writes),

  DB_BytesRead = SUM(num_of_bytes_read),

  DB_IOStallReadMS = SUM(io_stall_read_ms),

  DB_BytesWritten = SUM(num_of_bytes_written),

  DB_IOStallWriteMS = SUM(io_stall_write_ms),

  DB_IOStall = SUM(io_stall),

  DB_BytesSize = SUM(size_on_disk_bytes),

  DB_Files = dt.file_list

FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs

JOIN(

  SELECT

    vfs2.database_id,

    STUFF((

    SELECT ',' + mf3.name AS [text()]

    FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs3

    JOIN sys.master_files mf3 ON mf3.database_id = vfs3.database_id

      AND mf3.file_id = vfs3.file_id

    WHERE vfs2.database_id = vfs3.database_id

    FOR XML PATH('')

  ), 1, 1, '') AS file_list

  FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs2

  JOIN sys.master_files mf2 ON mf2.database_id = vfs2.database_id

  GROUP BY vfs2.database_id, DB_NAME(vfs2.database_id)

) dt ON dt.database_id = vfs.database_id

GROUP BY vfs.database_id, DB_NAME(vfs.database_id), dt.file_list

ORDER BY DB_NAME(vfs.database_id)

 

Excerpt from: http://www.sqlmag.com/article/tsql3/nifty-ways-to-use-for-xml-path-concatenation.aspx

Tags:

SQL Server

SQL Script to Document Database Roles/Permissions

by 11. September 2010 05:32

Script to Reverse Engineer SQL Server Object User Permissions

--Written By Bradley Morris (http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx)
--In Query Analyzer be sure to go to
--Query -> Current Connection Options -> Advanced (Tab)
--and set Maximum characters per column
--to a high number, such as 10000, so
--that all the code will be displayed.


DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'user_name_goes_here'

SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)

SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END

Tags:

SQL Server

SQL Convert Milliseconds into "HH:NN:SS" Format

by 20. August 2010 20:21

select

[Duration] as OriginalDuration,

case

  when isnumeric([Duration]) = 1 then

       convert(varchar(25), dateadd(ms, convert(int, [Duration]), '1/1/1900'), 108)

  else '0' + [Duration] end

  as StandardizedDuration

from FiveNine.CallDetails

-- Only sample records with non-zero values

where [Duration] <> '0' and [Duration] <> '0:00:00'

Tags:

SQL Server

SQL Script to Generate Table Inserts

by 20. August 2010 15:19

 

Stored procedure that will generate table inserts: sp_generate_inserts.zip (5.05 kb)

Tags:

SQL Server

SQL Select to Find Space Used for each Table

by 13. August 2010 17:08

create table #tbl

(

  [name] varchar(150)

, [rows] int

, [reserved] varchar(25)

, [data] varchar(25)

, [index_size] varchar(25)

, [unused] varchar(25)

)

declare @tbl varchar(100)

declare cur cursor for

select s.name + '.' + o.name from sys.objects o

join sys.schemas s on o.schema_id = s.schema_id

where type = 'U'

open cur

while 1=1 begin

  fetch next from cur into @tbl

  if @@fetch_status <> 0 break

  insert into #tbl exec sp_spaceused@tbl

end

close cur

deallocate cur

select

  name

, rows

, convert(int, replace(reserved, ' KB', '')) reserved_kb

, convert(int, replace(data, ' KB', '')) data_kb

,(convert(float, replace(data, ' KB', ''))/1024*100000)/100000 data_mb

,(convert(float, replace(data, ' KB', ''))/1048576*100000)/100000 data_gb

, convert(int, replace(index_size, ' KB', '')) index_size_kb

, convert(int, replace(unused, ' KB', '')) unused_kb

from #tbl

order by 4 desc

 

 

 

 

Tags:

SQL Server

SQL Conditional Inner/Outer Join on Foreign Key Table

by 1. July 2010 18:10

createtable

#ForeignKeyParentTable

(

  RecID intidentity(1,1)primarykey

, Bogus bitnotnull

)

 

createtable

#ForeignKeyChildTable

(

  RecID intidentity(1,1)primarykey

, ParentID intnotnull

,constraint fk_ForeignKeyParentChildTable foreignkey

  (ParentID)references #ForeignKeyParentTable(RecID)

)

 

-- Insert five records into parent

insertinto #ForeignKeyParentTable(Bogus)values(1)

insertinto #ForeignKeyParentTable(Bogus)values(1)

insertinto #ForeignKeyParentTable(Bogus)values(1)

insertinto #ForeignKeyParentTable(Bogus)values(1)

insertinto #ForeignKeyParentTable(Bogus)values(1)

 

-- Insert three related records into child

insertinto #ForeignKeyChildTable(ParentID)values(1)

-- skip parent 2

insertinto #ForeignKeyChildTable(ParentID)values(3)

-- skip parent 4

insertinto #ForeignKeyChildTable(ParentID)values(5)

 

-- Perform two select statements

-- once with the @IncludeAll off

-- and then with the @IncludeAll on

declare @IncludeAll int

set @IncludeAll =-1

while @IncludeAll < 1 begin

  set @IncludeAll = @IncludeAll + 1

  select

    p.RecID

  , c.RecID

  from #ForeignKeyParentTable p

  leftjoin #ForeignKeyChildTable c

  on p.RecID = c.ParentID

  where 1 =case

    when @IncludeAll = 1

    then 1 elsecase

      when c.RecID isnull

      then 0 else 1 endend

end

 

droptable #ForeignKeyParentTable

droptable #ForeignKeyChildTable

 

 

 

Tags:

SQL Server

SQL Script to Search All Tables and Columns for Text String

by 15. June 2010 16:19

This sript will search all columns of all tables in the database and look for matching text as entered in the @SearchStr variable.


set nocount on

 

declare

  @SearchStr nvarchar(1000)

, @SearchStr2 nvarchar(110)

, @TableName nvarchar(256)

, @ColumnName nvarchar(128)

 

set @SearchStr = 'Search text here...'

set @SearchStr2 = quotename('%' + @SearchStr + '%','''')

set @TableName = ''

 

create table #Results

(

  ColumnName nvarchar(370)

, ColumnValue nvarchar(3630)

)

 

while @TableName is not null begin

  set @ColumnName = ''

  set @TableName =

  (

     select min(quotename(TABLE_SCHEMA)

                + '.'

                + quotename(TABLE_NAME))

     from INFORMATION_SCHEMA.TABLES

     where TABLE_TYPE = 'BASE TABLE'

     and quotename(TABLE_SCHEMA)

         + '.'

         + quotename(TABLE_NAME)

         > @TableName

     and objectproperty(object_id(quotename(TABLE_SCHEMA)

                                  + '.'

                                  + quotename(TABLE_NAME)

                                  ), 'IsMSShipped') = 0

    ) -- set @TableName

  while (@TableName is not null) and(@ColumnName is not null) begin

    set @ColumnName =

    (

      select min(quotename(COLUMN_NAME))

      from INFORMATION_SCHEMA.COLUMNS

      where TABLE_SCHEMA = PARSENAME(@TableName, 2)

      and TABLE_NAME = PARSENAME(@TableName, 1)

      and DATA_TYPE IN('char', 'varchar', 'nchar', 'nvarchar')

      and quotename(COLUMN_NAME) > @ColumnName

    )

    if @ColumnName is not null begin

      insert into #Results

      exec

      (

         'select ''' + @TableName

          + '.'

          + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

         from ' + @TableName + ' (NOLOCK)

         where ' + @ColumnName + ' LIKE ' + @SearchStr2

       )

    end

  end

end

select ColumnName, ColumnValue from #Results

drop table #Results

 

 

 

Tags:

SQL Server

SQL Server Collation Conflict

by 30. April 2010 16:17

You may receive an error like the one shown below when joining two tables that have different collation settings.  This can happen commonly when using temporary tables if the TEMPDB database has different collation settings than the operational database.  For example: I had an environment where the TEMPDB had "SQL_Latin1_General_CP1_CI_AS" while the operational database had "Latin1_General_CI_AI".  In my case I created a SQL statement using a temporary table and then joined the temporary table to a table in the operational database and received the following error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. The resolution was to apply the COLLATE attribute to the joined column(s) as shown in sample below.

create table #tmp(DNIS char(10))

 

 

insert into #tmp values('0000000001')

insert into #tmp values('0000000002')

insert into #tmp values('0000000003')

insert into #tmp values('0000000004')

insert into #tmp values('0000000005')

 

 

select cd.DNIS

from CallDetails cd

join #tmp on cd.DNIS = #tmp.DNIS collate Latin1_General_CI_AI

 

 

Tags:

SQL Server

SQL Server Printing Long Text Fields

by 26. March 2010 20:11

declare @pos int, @len int, @txt varchar(500)

 

 

select @pos = 0, @len = datalength(TextColumnHere)

from TableNameHere where CriteriaHere

 

 

while (@pos * 500) < @len begin

  select @txt = substring(TextColumnHere, @pos * 500 + 1, 500)

  from TableNameHere where CriteriaHere

  print @txt + char(10)

  set @pos = @pos + 1

end

Tags:

SQL Server