number to datetime

convert(datetime ,43040.379150544 )
Till Next Time

image to varchar

convert(varchar(max),convert(varbinary(max),<>))


Till Next Time

MSSQL Check in which tables a customer appears (FK based)

I made a little script to check in which table a customer 'appeared':


SELECT
  'SELECT ''['+ +SCHEMA_NAME(f.SCHEMA_ID)+'].['+OBJECT_NAME(f.parent_object_id) + ']'' AS TABLE_NAME, COUNT(*) as AANTAL FROM ['+SCHEMA_NAME(f.SCHEMA_ID)+'].['+OBJECT_NAME(f.parent_object_id) +']
WHERE '+ COL_NAME(fc.parent_object_id,fc.parent_column_id) + '= ''<>''
UNION ALL' as [sql],
f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
where OBJECT_NAME (f.referenced_object_id) = '<>'


Till Next Time

Free space fixed drives

IF OBJECT_ID('tempdb..#drives') IS NOT NULL
    DROP TABLE #drives

CREATE TABLE #drives (
        drive char,
        [free] int
)
      
INSERT INTO #drives
EXEC master..xp_fixeddrives

SELECT drive, [free] / 1024 as FreeSpaceGB
FROM #drives


Till Next Time

Full backup databases starting with i8

/* Daily Full backup of i8* databases
   20140403 JJM (c) i8 solutions
*/

DECLARE @eSQL NVARCHAR(MAX)

DECLARE C cursor for
    select 'BACKUP DATABASE ['+name+'] TO  DISK = N''D:\i8\BU\'+ name +'.bak'' WITH NOFORMAT,
    INIT, 
    NAME = N'''+ name + '-Full Database Backup'',
    SKIP,
    NOREWIND,
    NOUNLOAD, 
    STATS = 10' from master.sys.databases
    where name like 'i8%'

OPEN C
FETCH NEXT FROM C INTO @eSQL

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@eSQL);
FETCH NEXT FROM C INTO @eSQL
END

CLOSE C
DEALLOCATE C
Till Next Time

MSSQL Connect to a database from a TSQL script

Step 1:

Switch to SQLCMD mode from the query menu in Management Studio:

Step 2:

Add the connection info using:

:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P password]]
Till Next Time

MSSQL select value into variable

Declare
@intTableCount int



set @intTableCount = (select COUNT(*) from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'Table_Name')

select @intTableCount
 
It will trow an error if you omit the ( and ) .
 
Till Next Time

mssql READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL


READ UNCOMMITTED

go
 
till next time

MSSQL SQL Server Agent appears with "Agent XPs disabled"

sp_configure 'show advanced options', 1;


GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE
 
Till Next Time

MSSQL startup norecovery

DBCC TRACEON (3607)
go

Till Next Time

MSSQL USING SYNONYMS FOR REMOTE CONNECTIONS

When using remote server connections in MSSQL they need to be in the format select * from [ServerName].[DatabaseName].[SchemaName].[TableName]. If you want to switch between two servers (say Production and Test) you only need to recreate the LinkedServer. The problem arises when the database and or schema name on the linked server is different. [ProductionData].[Pub] -> [TestData].[dbo]. This change would mean that you have to change every query which reference to the linked server.

A solution is using Synonyms instead. This means that you can use select * from [SynonymSchemaName].[Synonym] instead. The big advantage is that you can script the creating of synonyms, so a database connection switch can be done in seconds rather then hours.

This article contains a csp_ (Ciber Stored Procedure) for this task.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: John Minkjan

-- Create date: 20090223

-- Description: Automaticly creates synonyms for

-- table in (remote) database

-- v001 - initial version

-- =============================================

alter PROCEDURE csp_CreateSynonyms (

-- Add the parameters for the stored procedure here

@SeverName varchar(255),

-- holds the remote servername (optional)

@DatabaseName varchar(255),

-- holds the remote databasename ( NOT optional)

@SchemaName varchar(255),

-- holds the remote schemaname (Default: dbo)

@TargetSchemaName varchar(255),

-- holds the target schemaname for the synonyms (default: dbo)

@ReturnMessage varchar(400) output

)

AS

declare @sqlString varchar(4000)

-- holds the native SQL

declare @TableName varchar(255)

-- holds the TableName

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Cleanup @Servername

if ( ltrim(rtrim(@SeverName)) is null or

ltrim(rtrim(@SeverName)) = '' )

begin

set @SeverName = ''

end

else

begin

set @SeverName = ltrim(rtrim(@SeverName)) + '.'

end

-- Cleanup DataBase Name

set @DatabaseName = ltrim(rtrim(@DatabaseName)) + '.'

-- Cleanup @SchemaName

if ( ltrim(rtrim(@SchemaName)) is null or

ltrim(rtrim(@SchemaName)) = '' )

begin

set @SchemaName = 'dbo'

end

else

begin

set @SchemaName = ltrim(rtrim(@SchemaName))

end

-- Cleanup @TargetSchemaName

if ( ltrim(rtrim(@TargetSchemaName)) is null or

ltrim(rtrim(@TargetSchemaName)) = '' )

begin

set @TargetSchemaName = 'dbo'

end

else

begin

set @TargetSchemaName = ltrim(rtrim(@TargetSchemaName))

end

-- Set the Cursor

set @sqlString = 'declare SynonymCur cursor for ' +

'SELECT t1.name AS TableName ' +

'FROM (SELECT * '+

'FROM '+ @SeverName + @DatabaseName + 'sys.objects) AS t1 INNER JOIN ' +

'(SELECT * ' +

'FROM '+ @SeverName + @DatabaseName + 'sys.schemaS) AS t2 ON t1.schema_id = t2.schema_id '+

'WHERE (t1.type = ''U'') and t2.name = '+ CHAR(39) + @SchemaName + CHAR(39)

exec (@sqlString)

Open SynonymCur

fetch next from SynonymCur into @TableName

while @@fetch_status=0

begin

-- Create and Execute Drop Statemen

set @sqlString = 'IF EXISTS (SELECT * FROM ' +

'sys.synonyms WHERE name = N'+ CHAR(39)+ @TableName+ ''') ' +

'DROP SYNONYM ['+ @TargetSchemaName + '].[' + @TableName+ '] '

exec (@sqlString)

-- Create and Execute Create Statement

set @sqlString = 'CREATE SYNONYM ['+ @TargetSchemaName + '].[' + @TableName+ '] ' +

'FOR '+ @SeverName + @DatabaseName + '['+ @SchemaName + '].[' + @TableName+ '] '

PRINT @sqlString

exec (@sqlString)

fetch next from SynonymCur into @TableName

end

-- Clean up

close SynonymCur

deallocate SynonymCur

-- Feedback

set @ReturnMessage = 'Succes'

END

GO

Till Next Time

MSSQL add linkend server

http://doc.ddart.net/mssql/sql70/sp_adda_17.htm

USE master
GO
EXEC sp_addlinkedserver
'SEATTLESales',
N'SQL Server'
GO

Till Next Time

MSSQL Restore DB using TSQL

Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
GO

Step 2: Use the values in the LogicalName Column in following Step.
—-Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE


—-Restore Database
RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

MSSQL Last of Month

select dateadd(Month,1, ( DateAdd(Month,DateDiff(Month,0,getdate()),0)) ) -1

Till Next Time

MSSQL First of Month

DateAdd(Month,DateDiff(Month,0,'11/30/2005'),0)

Till Next Time

MSSQL Column info

SELECT * FROM information_schema.columns

Till Next Time

MSSQL Update based on other table

UPDATE Table1
SET Table1.Column1 = Table2.Column1
FROM Table1 INNER JOIN
Table2 ON
Table1.ColumnKey = Table2.ColumnKey
Till Next Time

MSSQL Scripted indexes

Dimension tables:

/*
(re)creating all dimensional indexes
within a schema in one go
for dimension tables
20081219 JJM: First
darft
*/
declare @strSql01 varchar(4000)
declare @strSql02
varchar(4000)
declare @strTableName varchar(500)
declare @strColumnName
varchar(500)
declare @strIndexFileGroup varchar(500)
declare
@strSchemaName varchar(500)
set @strIndexFileGroup = 'INDEX'
set
@strSchemaName = 'dbo'
declare idx_cur cursor for
SELECT distinct
table_name, column_name FROM information_schema.columns
WHERE Table_Name
like 'Dim_%'
and Column_name like '%Code%'
and table_schema =
@strSchemaName
and table_name in (SELECT name FROM sys.objects WHERE name
like 'Dim_%'
AND type in (N'U')
) order by table_name
open
idx_cur
fetch next from idx_cur into @strTableName,@strColumnName
while
@@fetch_status=0
begin
set @strSql01 = 'IF EXISTS (SELECT * FROM
sys.indexes WHERE object_id =
OBJECT_ID(N''['+@strSchemaName+'].['
+@strTableName
+']'') AND name =
N''IX_'
+@strTableName +'_' +@strColumnName +''')
DROP INDEX
[IX_'
+@strTableName +'_' +@strColumnName +'] ON
['+@strSchemaName+'].['
+@strTableName
+'] WITH ( ONLINE = OFF
)'
print @strSql01
exec (@strSql01)
set @strSql02 = 'CREATE
NONCLUSTERED INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON
['+@strSchemaName+'].['
+@strTableName
+'] ([' + @strColumnName
+']
ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, '
+ 'SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, '
+'ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ['+ @strIndexFileGroup
+']'

print @strSql02
exec (@strSql02)
fetch next from idx_cur
into @strTableName,@strColumnName
end
close idx_cur
deallocate
idx_cur

Fact tables:

/*
(re)creating all dimensional indexes within a schema in one go
for facttables
20081219 JJM: First darft
*/
declare @strSql01 varchar(4000)
declare @strSql02 varchar(4000)
declare @strTableName varchar(500)
declare @strColumnName varchar(500)
declare @strIndexFileGroup varchar(500)
declare @strSchemaName varchar(500)

set @strIndexFileGroup = 'INDEX'
set @strSchemaName = 'dbo'

declare idx_cur cursor for
SELECT distinct table_name, column_name FROM information_schema.columns
WHERE Table_Name like 'Feit_%'
and Column_name like 'Dim_%'
and table_schema = @strSchemaName
and table_name in (SELECT name FROM sys.objects WHERE name like 'Feit_%'
AND type in (N'U')
) order by table_name

open idx_cur
fetch next from idx_cur into @strTableName,@strColumnName
while @@fetch_status=0
begin

set @strSql01 = 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''['+@strSchemaName+'].['
+@strTableName
+']'') AND name = N''IX_'
+@strTableName +'_' +@strColumnName +''')
DROP INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON ['+@strSchemaName+'].['
+@strTableName
+'] WITH ( ONLINE = OFF )'

print @strSql01
exec (@strSql01)

set @strSql02 = 'CREATE NONCLUSTERED INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON ['+@strSchemaName+'].['
+@strTableName
+'] ([' + @strColumnName
+'] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, '
+ 'SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, '
+'ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ['+ @strIndexFileGroup +']'


print @strSql02
exec (@strSql02)

fetch next from idx_cur into @strTableName,@strColumnName
end

close idx_cur
deallocate idx_cur

Till Next Time

MSSQL Rowcount + trending

/* AUTOMATIC TABLE COUNT AND TREND LOGGING */
/* John Minkjan Ciber Nederland */
/* 20081205 v001 intial version */
/* Decalartions */
DECLARE @table_name varchar(250)
DECLARE @strSQL varchar(500)
Declare @SQL varchar(2000)
DECLARE @TABLE_COUNT INT
Declare @TableCount_ID Int
declare @TableCountTrend varchar(50)
/* Part 1 Get the counts */
DECLARE table_cursor CURSOR FOR
SELECT name FROM sys.objects
WHERE type in (N'U')
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'DECLARE Dyn_cursor CURSOR
FOR SELECT COUNT(*) FROM ' + @table_name
Exec(@SQL)
OPEN Dyn_cursor
FETCH NEXT FROM Dyn_cursor
INTO @TABLE_COUNT
CLOSE Dyn_cursor
DEALLOCATE Dyn_cursor
INSERT INTO [XXXXXX].[dbo].[AUD_TableCount]
([TableName]
,[TableCountDate]
,[TableCount])
VALUES
(@table_name
,GETDATE()
,@TABLE_COUNT)
FETCH NEXT FROM table_cursor
INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
/* Part 2 Set the trends */
DECLARE TREND_CURSOR CURSOR FOR
(select t1.TableCount_ID ,
case when t1.tablecount - t2.tablecount = 0 then 'NONE' ELse
case when t1.tablecount - t2.tablecount > 0 then 'UP' else 'DOWN' end end
as TableCountTrend from
(SELECT TableCount_ID,
TableName,
TableCountDate,
TableCount,
ROW_NUMBER() OVER(PARTITION BY TableName ORDER BY TableCountDate ASC) RowVersion
FROM [AMIGO_LOG].[dbo].[AUD_TableCount]) T1,
(SELECT TableName,
TableCountDate,
TableCount,
ROW_NUMBER() OVER(PARTITION BY TableName ORDER BY TableCountDate ASC) RowVersion
FROM [AMIGO_LOG].[dbo].[AUD_TableCount]) T2
where
t1.tablename = t2.tablename and
t1.rowversion-1 = t2.rowversion and
t1.TableCountDate >= getdate()-90)
OPEN TREND_CURSOR
FETCH NEXT FROM TREND_CURSOR
INTO @tableCount_ID, @TableCountTrend
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM TREND_CURSOR
INTO @tableCount_ID, @TableCountTrend
UPDATE [AMIGO_LOG].[dbo].[AUD_TableCount]
SET [TableCountTrend] = @TableCountTrend
WHERE TableCount_ID= @tableCount_ID
END
CLOSE TREND_CURSOR
DEALLOCATE TREND_CURSOR

till next time

MSSQL Q&D Rowcount

select Distinct
object_name(id)
, rowcnt
from
sysindexes
where object_name(id) not like 'sys%'

Till Next Time