SQL: DB Diverse

15 posts / 0 new
Last post
benzzon
benzzon's picture
SQL: DB Diverse
*** SQL Server Books Online:
 
*** Korrigera users efter restore av databas:
sp_change_users_login Auto_Fix, '@username@'
(sp_change_users_login Update_One, '@username@', '@username@')

*** Krympa data & log i en databas:
use @database@
dump tran @database@ with no_log

dbcc shrinkfile ('@database_log@',0)

dbcc shrinkfile ('@database_data@',0)
 

DUMP TRANSACTION tempdb WITH NO_LOG

DBCC OPENTRAN (tempdb)

DBCC SHRINKDATABASE (tempdb, 80) -- Try to achieve 80 percent free space..

 

-- "SQL2008 Dump transaction", set to SIMPLE mode

ALTER DATABASE HD4_FI2_GU SET RECOVERY SIMPLE

go

use HD4_FI2_GU

go

-- Shrink the db

DBCC SHRINKFILE ('HD4_FI2_GU_Log', 1);

go

*** Stänga av transaktionsloggning:
 
-- SQL 2000:

sp_dboption @database@,'trunc. log on chkpt.',true

 -- SQL 2005:
ALTER DATABASE @database@ SET RECOVERY SIMPLE
 
 
 
*** Kolla devicenamn för databas: sp_helpdb @database@
 
*** Hämta device-info ur backup fil: RESTORE FILELISTONLY FROM DISK='c:\sample.bck'
 
*** Skapa select & delete satser utifrån sysobjects: -- *** Användbart för "fritextsökning" för att hitta data..

select 'select ' + '''' + so.name + '''' + ',* from ' + su.name + '.' + so.name  from sysobjects so left outer join sysusers su on so.uid=su.uid  where so.type = 'u' order by so.name

OLD: select 'select ' + '''' + name + '''' + ',* from ' + name from sysobjects where type = 'u' order by name
 
-- *** Användbart för att snabbt tömma en databas på data..
select 'delete from ' + name from sysobjects where type = 'u' order by name

-- *** Avstämning av objekt i databas (för jämförelse mot andra databaser ex.vis..)
select name,type from sysobjects order by type,name

-- *** Visa tabell & fältnamn, för att ex.vis kunna söka efter visst fält..)
select so.type,so.name,sc.name from sysobjects so, syscolumns sc where sc.id=so.id and so.type in ('U','V') order by so.type,so.name,sc.colorder

*** Uppdatera vyer, vid förändring av ingående kolumner i underliggande tabeller.
select 'sp_refreshview ' + '''' + name + '''' from sysobjects where xtype='V' order by name

 

*** Ange kompatibilitet med SQL 2000 (för SQL 2005 databas).

sp_dbcmptlevel '@database@', 80

backup via script (INIT för överskrivning av ev befintlig fil):
BACKUP DATABASE @dbname@ TO DISK = 'c:\@dbname@.bak' WITH INIT

 

 *** Sök och ersätt "vbCrLf" med blanktecken:

select replace(HD_DocDescr,char(13)+char(10),' ') from Document where Document_ID='10004455'


*** SQL Server Backup, Integrity Check, and Index and Statistics Maintenance, Ola Hallengren:
http://ola.hallengren.com/


-- *** Sample to rebuild or reorganize all indexes in specified database.
EXECUTE master.dbo.IndexOptimize
 @Databases = 'database-name-here',
 @FragmentationLow = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @Indexes = 'ALL_INDEXES',
 @UpdateStatistics = 'ALL'


*** Beräkna checksumma (checksum) för fält av typ image/binary.
select sys.fn_repl_hash_binary(CR_DocData) [doc_checksum] from tblCR_RptDoc
 

benzzon
benzzon's picture
.

Avgöra om sql 2000, eller sql 2005:

if left(convert(varchar,serverproperty('productversion')),1) = '8'

select '2000'

GO

if left(convert(varchar,serverproperty('productversion')),1) = '9'

select '2005'

GO

benzzon
benzzon's picture
.
 
*** "User Permissions Report" (SQL2000):

 

select

 sysusers.name as username, sysusers.gid,

 sysobjects.name as objectname, sysobjects.id,

 CASE WHEN sysprotects_1.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'SELECT',

 CASE WHEN sysprotects_2.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'INSERT',

 CASE WHEN sysprotects_3.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'UPDATE',

 CASE WHEN sysprotects_4.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'DELETE',

 CASE WHEN sysprotects_5.action is null THEN CASE WHEN sysobjects.xtype = 'U' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'EXECUTE'

from

 sysusers

 full join sysobjects on ( sysobjects.xtype in ( 'P', 'U' ) and sysobjects.Name NOT LIKE 'dt%' )

 left join sysprotects as sysprotects_1

  on sysprotects_1.uid = sysusers.uid and sysprotects_1.id = sysobjects.id and sysprotects_1.action = 193 and sysprotects_1.protecttype in ( 204, 205 )

 left join sysprotects as sysprotects_2

  on sysprotects_2.uid = sysusers.uid and sysprotects_2.id = sysobjects.id and sysprotects_2.action = 195 and sysprotects_2.protecttype in ( 204, 205 )

 left join sysprotects as sysprotects_3

  on sysprotects_3.uid = sysusers.uid and sysprotects_3.id = sysobjects.id and sysprotects_3.action = 197 and sysprotects_3.protecttype in ( 204, 205 )

 left join sysprotects as sysprotects_4

  on sysprotects_4.uid = sysusers.uid and sysprotects_4.id = sysobjects.id and sysprotects_4.action = 196 and sysprotects_4.protecttype in ( 204, 205 )

 left join sysprotects as sysprotects_5

  on sysprotects_5.uid = sysusers.uid and sysprotects_5.id = sysobjects.id and sysprotects_5.action = 224 and sysprotects_5.protecttype in ( 204, 205 )

where

 (sysprotects_1.action is not null or sysprotects_2.action is not null or

 sysprotects_3.action is not null or sysprotects_4.action is not null or

 sysprotects_5.action is not null)

order by

 sysusers.name, sysobjects.name

 
 

 

benzzon
benzzon's picture
 .
keywords: bcp transfer sql data
 
bcp export: bcp <database>..<table> out C:\<table>.Dat -n -k -E -S<server> -U<username> -P<password>
 
bcp import: bcp <database>..<table> in C:\<table>.Dat -n -k -E -S<server> -U<username> -P<password>

-n: "native"
-k: "keep null values"
-E: "keep identity values"

-- *** Create CMD-line executable script, for export (OUT) and to be used in .bat-file..
select 'bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.bcp" -k -E -n -S'
+ @@servername + ' -Usa -Ppassword >> "c:\bcp\bcp_out.log"'
from sysobjects where xtype = 'U'

-- *** Create CMD-line executable script, for import (IN) and to be used in .bat-file..
select 'bcp ' + 'HD1_FI2_XXX' + '..' + name + ' in "c:\bcp\' + name + '.bcp" -k -E -n -S'
+ 'srv-name\srv-instance' + ' -Usa -Ppassword -e "c:\bcp\bcp_in_err.log" >> "c:\bcp\bcp_in.log"'
from sysobjects where xtype = 'U'
 

 

benzzon
benzzon's picture
.

Sökord: sql servicepack

SQL Server 2005 Version (service packs):
 

Version @@VERSION MsDtsSrvr.exe File Version Registry Version Registry Patch Level
Release to Manufacturing 9.00.1399.00 9.0.1399.0 9.0.1399.06 9.0.1399.06
Service Pack 19.00.2047.00 9.0.2047.0 9.1.2047.00 9.1.2047.00
Post SP1 Cumulative Hotfix 9.0.2153.00 9.0.2153.0 9.1.2047.00 9.1.2153
Service Pack 2 CTP9.00.3027.00 9.0.3027.0 9.2.3027.00 9.2.3027.00
Service Pack 29.00.3042.00 9.0.3042.0 9.2.3042.00 9.2.3042
Service Pack 2 Rollup 3 9.00.3186.0 9.00.3186.00 9.2.3042.00 9.2.3186

 

 

Microsoft SQL Server 2005 Service Pack 3 (9.00.4035)
http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en

 
 
Quick Summary (http://sqlserverbuilds.blogspot.com/):

Quick summary:

 RTM (Gold, no SP)SP1SP2SP3SP4
SQL Server 2012
codename Denali
11.00.2100.6011.00.3000   
SQL Server 2008 R2
codename Kilimanjaro
10.50.1600.110.50.250010.50.4000  
SQL Server 2008
codename Katmai
10.00.1600.2210.00.253110.00.400010.00.5500 
SQL Server 2005
codename Yukon
9.00.1399.069.00.20479.00.30429.00.40359.00.5000
SQL Server 2000
codename Shiloh
8.00.1948.00.3848.00.5328.00.7608.00.2039
SQL Server 7.0
codename Sphinx
7.00.6237.00.6997.00.8427.00.9617.00.1063
 
benzzon
benzzon's picture
.
 
List permissions for given role:
Resultat kan kopieras till excel, och formel
="grant " & EXTEXT(C1;5;LÄNGD(C1)) & " on " & B1 & " to sql-role"
kan användas för att skapa grant-script.

 

/****** Stored Procedure dbo.sp_showpermissions v 1.0 ******/

/****** Created 07/23/1998 by Brent Huscher ******/

/****** Modified 07/23/1998 by Brent Huscher ******/

 

CREATE PROCEDURE sp_showpermissions

 

@group VARCHAR(30) = NULL

 

AS

 

SET NOCOUNT ON

 

IF @group is NULL SELECT @group = 'public'

 

IF EXISTS (SELECT name from sysusers where name = @group and uid = gid)

 

BEGIN

 

SET NOCOUNT OFF

 

SELECT "ROLE NAME" = left(b.name, 30),

"OBJECT NAME" = left(c.name, 30),

"ACTION" = CASE a.action

WHEN 26 THEN ' 26 REFERENCES'

WHEN 193 THEN '193 SELECT'

WHEN 195 THEN '195 INSERT'

WHEN 196 THEN '196 DELETE'

WHEN 197 THEN '197 UPDATE'

WHEN 198 THEN '198 CREATE TABLE'

WHEN 203 THEN '203 CREATE DATABASE'

WHEN 204 THEN '204 GRANT_W_GRANT'

WHEN 205 THEN '205 GRANT'

WHEN 206 THEN '206 REVOKE'

WHEN 207 THEN '207 CREATE VIEW'

WHEN 222 THEN '222 CREATE PROCEDURE'

WHEN 224 THEN '224 EXECUTE'

WHEN 228 THEN '228 DUMP DATABASE'

WHEN 233 THEN '233 CREATE DEFAULT'

WHEN 235 THEN '235 DUMP TRANSACTION'

WHEN 236 THEN '236 CREATE RULE'

END,

"TYPE" = CASE c.type

WHEN 'C' THEN 'C CHECK constraint'

WHEN 'D' THEN 'D Default or DEFAULT constraint'

WHEN 'F' THEN 'F FOREIGN KEY constraint'

WHEN 'K' THEN 'K PRIMARY KEY or UNIQUE constraint'

WHEN 'L' THEN 'L Log'

WHEN 'P' THEN 'P Stored procedure'

WHEN 'R' THEN 'R Rule'

WHEN 'RF' THEN 'RF Stored procedure for replication'

WHEN 'S' THEN 'S System table'

WHEN 'TR' THEN 'TR Trigger'

WHEN 'U' THEN 'U User table'

WHEN 'V' THEN 'V View'

WHEN 'X' THEN 'X Extended stored procedure'

END

FROM sysprotects a, sysusers b, sysobjects c

WHERE a.uid = b.uid

AND c.id = a.id

AND b.name = @group

ORDER BY b.name, c.name, a.action

 

END

 

ELSE

 

BEGIN

PRINT 'You did not provide a valid role name'

PRINT ''

SELECT 'Valid role names for the ' + db_name() + ' database are:'

PRINT ''

SELECT "Role Name"=name FROM sysusers WHERE uid = gid ORDER BY name

PRINT ''

PRINT ''

PRINT 'Syntax: sp_showpermissions [role_name]'

PRINT ''

PRINT 'sp_showpermissions with no parameter will show permissions for public'

END

 

SET NOCOUNT OFF

 

benzzon
benzzon's picture
.

use tempdb

go

sp_adduser 'someuser'

go

sp_addrolemember 'db_owner', 'someuser'

go

 
 
DUMP TRANSACTION tempdb WITH NO_LOG

go

--DBCC OPENTRAN (tempdb)

DBCC SHRINKDATABASE (tempdb, 80) -- Try to achieve 80 percent free space..

go

use tempdb

go

dbcc shrinkfile ('templog',0)

go

dbcc shrinkfile ('tempdev',0)

go

 

 

 

benzzon
benzzon's picture
.
Deadlock-debugging with SQL Profiler, use statements below
to find table/object with locks.
 
Use SQL Profiler with logging of "Deadlock graph", the image shows
a "associated objid", use this number as "partition_id":
 
 

SELECT object_name(object_id)

FROM sys.partitions

WHERE partition_id = <number> -- for example: 72057594213892096

 
 

-- HD4_FI2_GU:

-- 72057594213302272 = tblCR_UserData_T1

-- 72057594213892096 = tblCR_UserData_T3

 
Att testa:
 

SELECT snapshot_isolation_state FROM sys.databases WHERE name= 'HD4_FI2_GU' -- 0 = OFF

 ALTER DATABASE HD4_FI2_GU SET ALLOW_SNAPSHOT_ISOLATION ON

 

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'HD4_FI2_GU' -- 0 = OFF

 ALTER DATABASE HD4_FI2_GU SET READ_COMMITTED_SNAPSHOT ON

Bok med info ang deadlocks:
 
"SQL Server 2008 Query Performance Tuning Distilled"
 
Indexet idxSTRUCT_FLOOR på tblCR_UserData_T1..?

 

benzzon
benzzon's picture
.

-- *** Generate list with columns and datatypes of a table..

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  

        T.[name] AS [table_name], AC.[name] AS [column_name],  

        TY.[name] AS system_data_type, AC.[max_length], 

        AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded] 

FROM sys.[tables] AS T  

  INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 

 INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]  

WHERE T.[is_ms_shipped] = 0

and T.name='tblCR_UserData_T1'

ORDER BY T.[name], AC.[column_id]

 

benzzon
benzzon's picture
Solution/workaround to

Solution/workaround to problem when trying to execute a large script-file with "SQL Server Management Studio", which causes "Insufficient memory to continue the execution of the program. (mscorlib)"

Instead run "SQLCMD", syntax:

sqlcmd -S <server-name> -d <db-name> -i sqlfile.sql -o outputfile.txt

(uncertain about "-o" option, but an output-file is useful when running large script-file..)
 

benzzon
benzzon's picture
Script for viewing SSRS execution log..

use ReportServer$K8
go

SELECT TOP 100 elog.status, elog.TimeStart, elog.*
FROM [dbo].[ExecutionLog2] elog
WHERE elog.reportPath like ('%cfabrpt1%')
AND elog.UserName like ('%nilben%')
ORDER BY elog.timestart DESC
go
 

benzzon
benzzon's picture
SQL-conversion, get numeric value of string..
Keywords: convert cast
Almost like a "val" function:

declare @val_field varchar(10)
select @val_field='999'
 
SELECT
CASE ISNUMERIC(@val_field)
 WHEN 1 THEN 
	CAST(@val_field as float)
 WHEN 0 THEN
	null
 ELSE null
END as [COLUMN_NAME]

benzzon
benzzon's picture
INFO: Show size of all tables (sp_spaceused)
-- *** Script to list size of all tables..
CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
       unused VARCHAR(18))
 
EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '
 
SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
            CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes 
ORDER BY   NumberOfRows DESC,SizeinKB DESC,TableName
 
DROP TABLE #RowCountsAndSizes 

benzzon
benzzon's picture
Script to calculate estimate time after kill / rollback..

-- *** Estimated time to complete...?
-- Script copied from http://allen-mcguire.blogspot.se/2013/02/estimated-time-to-complete.html
 

SELECT percent_complete AS 'PctComplete'
,start_time AS 'StartTime'
,command AS 'Command'
,b.name AS 'DatabaseName'
,DATEADD(ms, estimated_completion_time, GETDATE()) AS 'EstimatedEndTime'
,( estimated_completion_time / 1000 / 60 ) AS 'EstimatedMinutesToEnd'
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE estimated_completion_time > 0

-- Kill "with statusonly" might also be useful:
KILL [Session ID] WITH STATUSONLY
 

benzzon
benzzon's picture
Copy sql-login passwords (hash-nr) from one server to another
-- *** http://www.sqlservercentral.com/blogs/sqlstudies/2013/03/25/
how-do-i-move-a-sql-login-from-one-server-to-another-without-the-password/

SELECT 'CREATE LOGIN MyLogin WITH PASSWORD = ' +
 CONVERT(varchar(max), LOGINPROPERTY('MyLogin', 'PasswordHash'),1 ) + ' HASHED'