Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

List of Ascii values

SQL Server 2005 Performance Dashboard download

OUTPUT


-- INSERT INTO.... OUTPUT INSERTED
INSERT INTO HR.Staff ( FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
    (
        StaffID,
        NotificationDate,
        NotificationType
    )
VALUES  ( 'Santa','Claus')
-- UPDATE .... OUTPUT
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT deleted.DocumentSummary, 
       inserted.DocumentSummary 
    INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation'

-- save the results of a DELETE statement into a table variable
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621

Get FOREIGN KEYs Of DB

select  
f.constraint_object_id, object_name(f.constraint_object_id) as constraint_object,
f.constraint_column_id, --object_name(f.constraint_column_id) as constraint_column, --FK name
f.parent_object_id, object_name(f.parent_object_id) as parent_object, --FK table
f.parent_column_id, object_name(f.parent_column_id) as parent_column, --FK column
f.referenced_object_id, object_name(f.referenced_object_id) as referenced_object, --PK table
f.referenced_column_id --, object_name(f.referenced_column_id) as referenced_column --PK column
from sys.foreign_key_columns f
inner join sys.objects o on o.object_id = f.constraint_object_id 
   and SCHEMA_NAME(o.schema_id) <> 'dbo'   -- filter by scema of the constraint
where object_name(referenced_object_id) like '%XXX%'   --filter by table name

Dynamic insert statement into table variable

-- variable table declaration:
declare @TempObjects table (schema_ nvarchar(50), table_ nvarchar(250))

-- set the select statement into the string variable:
declare @SQL as nvarchar(2000)
SET @SQL = 'SELECT..........'

-- insert the string into the table:
insert into @TempObjects(schema_, table_) 
EXEC(@SQL)

Display Foreign Key Relationships


select
f.constraint_object_id, o.name as constraint_object,
f.constraint_column_id, --object_name(f.constraint_column_id) as constraint_column,
f.parent_object_id, object_name(f.parent_object_id) as parent_object,
f.parent_column_id, object_name(f.parent_column_id) as parent_column,
f.referenced_object_id, object_name(f.referenced_object_id) as referenced_object,
f.referenced_column_id --, object_name(f.referenced_column_id) as referenced_column
from sys.foreign_key_columns f
-- filter by schema:
inner join sys.objects o on o.object_id = f.constraint_object_id  and SCHEMA_NAME(o.schema_id) = 'dbo'


Another good script:

CROSS APPLY Error: Incorrect syntax near 'M'

To use APPLY, the database compatibility level must be at least 90. Check the compatibility level and update it if required:

If you can't update compatibility level for some reason, replace CROSS APPLY to Join:
select f.* from TableM M
cross apply dbo.udf_MembersOfM (79706,@cDate,M.ID,null,null) f 
-->
select f.* from TableM  M
inner join dbo.udf_MembersOfM (79706,@cDate,null,null,null)  f on and M.ID = f.ID

Compatibility level

-- get current compatibility level of DB
sp_dbcmptlevel 'AdventureWorks2008R2'

-- alter DB compatibility level
ALTER DATABASE AdventureWorks2008R2 SET COMPATIBILITY_LEVEL = 90
--or:
EXEC sp_dbcmptlevel AdventureWorks2008R2, 90

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. 
Therefore, the best practice to change the compatibility level of database is in following three steps:
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER
GO
-- alter DB compatibility (2 options)
GO
ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER
GO

Before upgrading Compatibility Level, it's good to check if the DB is ready to this upgrade, by one or more of those 2 ways:
1. run the Upgrade advisor
2. Script: testing all SPs before upgrading to Compatibility Level 90: 
http://www.sqlservercentral.com/scripts/Compatibility/62093/

Values of compability levels:
60 = SQL Server 6.0 
65 = SQL Server 6.5 
70 = SQL Server 7.0 
80 = SQL Server 2000 
90 = SQL Server 2005 
100= SQL Server 2008

two digit year cutoff

-- show settings:
EXEC sp_configure 'two digit year cutoff'
GO

-- two digit year cutoff configuration:
EXEC sys.sp_configure N'two digit year cutoff', N'2030'
GO
RECONFIGURE WITH OVERRIDE
GO
-- or In Object Explorer, in the server Properties

By default, SQL Server interprets two-digit years based on a cutoff year of 2049:
SELECT CAST('02/02/02' AS DATETIME), 
CAST('02/02/49' AS DATETIME),
CAST('02/02/50' AS DATETIME)
-- output:  2002-02-02,2049-02-02,1950-02-02

Specifying four-digit years is Always recommended.

Add new FileGroup and new file to DB

ALTER DATABASE [<DB_NAME>] ADD FILEGROUP New_FileGroup 
GO
ALTER DATABASE [<DB_NAME>] ADD FILE(NAME = N'New_FileGroup', FILENAME = N'E:\BackUp\DB_NAME_New_FileGroup.ndf' , SIZE = 50, FILEGROWTH = 5) TO FILEGROUP New_FileGroup
GO

SQL Paging in SQL Server 2011

ORDER BY OFFSET n ROWS FETCH NEXT n ROWS ONLY
Available only from SQL Server 2011 (Denali).

SELECT ..... FROM .....
ORDER BY ColumnA
  OFFSET 10 ROWS -- skip the first 10 rows
  FETCH NEXT 10 ROWS ONLY --return only 10 rows

Until MSSQL 2011 we do 'Paging' using:
SELECT ..., ROW_NUMBER ( ) OVER ( ORDER BY columnA) AS ROW FROM ... WHERE ROW BETWEEN 51 AND 100

 And here a sample to SP that return a page in paging list:
CREATE PROCEDURE SqlPagingSP
(
  @PageNumber int,
  @RowsPerPage int
)
AS

SELECT .....
FROM ......
ORDER BY ColumnA
  OFFSET (@PageNumber-1)*@RowsPerPage ROWS
  FETCH NEXT @RowsPerPage ROWS ONLY
GO

Multiple choice in the Generate Scripts wizard

Maybe it's Obvious, but I didn't know that multiple choice is possible in the Generate Scripts wizard until yesterday.... and when I found it, I saved a lot of time... :-)

The selection work like multiple choice of files in folder window: 
select the first object and then select the last object with Shift.

Sign/unsign one of the selected objects, and all the others also will be sign/unsign.


Find columns with different collation

use [<DB_NAME>]
GO

DECLARE @NewCollation sysname
SET @NewCollation = 'Latin1_General_CI_AI' -- or any other Collation....
SELECT
T.TABLE_NAME, C.COLUMN_NAME, T.TABLE_SCHEMA, C.DATA_TYPE,
C.CHARACTER_MAXIMUM_LENGTH, C.IS_NULLABLE, C.COLLATION_NAME,
-- alter collation statement
'ALTER TABLE ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME +
' ALTER COLUMN ' + C.COLUMN_NAME+ ' ' + C.DATA_TYPE +
CASE WHEN C.CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)'  
WHEN C.DATA_TYPE IN ('text', 'ntext') THEN ''  
ELSE '(' + CAST (C.CHARACTER_MAXIMUM_LENGTH AS nvarchar(10)) + ')'  
END +
' COLLATE ' + @NewCollation + ' ' + 
CASE C.IS_NULLABLE WHEN 'NO' THEN 'NOT NULL' ELSE 'NULL' END 
FROM INFORMATION_SCHEMA.COLUMNS C 
JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME 
WHERE C.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext', 'sysname') 
AND C.COLLATION_NAME <> @NewCollation 
AND T.TABLE_TYPE = 'BASE TABLE' 
order by T.TABLE_NAME


Execute as specific user

WITH EXECUTE AS 'User1'

Execute an object as specific user - in functions (except inline table-valued functions), procedures, queues, and triggers.

Example:
-- login with dbo

CREATE PROCEDURE dbo.ExecuteAs_Demo
WITH EXECUTE AS 'Lms'
AS
SELECT user_name()
EXECUTE AS CALLER;
SELECT user_name(); -- Shows execution context is set to SqlUser2, the caller of the module.
REVERT;
SELECT user_name(); -- Shows execution context is set to SqlUser1.
GO

SELECT user_name() --result: dbo
exec  dbo.ExecuteAs_Demo --result: Lms, dbo, Lms

How to get today's date

DECLARE @Today DATETIME, @Tomorrow DATETIME

SELECT -- @Today = today 00:00:00, @Tomorrow = tomorrow 00:00:00
   @Today = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'), 
   @Tomorrow = DATEADD(DAY, 1, @Today) 
select @Tomorrow = DATEADD(mi, -1, @Tomorrow) -- @Tomorrow = today 23:59:59
SELECT @Today ,@Tomorrow

Generate scripts for all the SPs in the DB

-- of course you can replace objects type (not only SPs - all the objects that can use sp_helptext)
-- if you don't need the drop part - remark it


DECLARE @Cmd nvarchar(max), @Object nvarchar(1000)
DECLARE CursorDelSPs CURSOR FORWARD_ONLY  FOR 
-------------- Start Select --------------------------
select SCHEMA_NAME(schema_id) + '.' + name from sys.objects 
where type in ('P', 'PC', 'RF', 'X')
and SCHEMA_NAME(schema_id) = 'dbo'
-------------- End Select ----------------------------
OPEN CursorDelSPs
FETCH NEXT FROM CursorDelSPs 
INTO @Object
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@Object) AND type in ('P', 'PC', 'RF', 'X'))
BEGIN
-- drop
print 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(' + @Object + ') AND type in (''P'', ''PC'', ''RF'', ''X''))
DROP PROCEDURE ' + @Object + '
GO
'
--header
print '/****** Object:  StoredProcedure ' + @Object + '    Script Date: ' + Convert(varchar, getdate(), 121) + ' ******/'
print 'SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO'
-- body
SET @Cmd = 'sp_helptext ''' + @Object + ''''
EXEC (@Cmd)

-- footer
print 'GO'
END

--Get next record from cursor
FETCH NEXT FROM CursorDelSPs 
INTO @Object
END
CLOSE CursorDelSPs
DEALLOCATE CursorDelSPs

Server memory usage

EXEC sp_configure 'show advanced options', 1 -- must be in an advanced mode
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory (MB)', 512 --make the actual change 
RECONFIGURE WITH OVERRIDE 
--The RECONFIGURE WITH OVERRIDE is necessary in order for the change to take effect immediately
GO
RECONFIGURE;
GO
-- Set ‘show advanced options’ back to default:
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

-- check the current max memory usage
EXEC sp_configure 'max server memory (MB)'

While memory setting is recommended to remember that there are other processes that should run on your computer and operating system itself must memory. Recommended to leave 1-2GB to the operating system.

Get performance counters maintained by the server

SELECT * FROM sys.dm_os_performance_counters

For information about each performance counter, see:

Unicode and Collation in SQL

Collation at four levels:
  1. Server
  2. Database. Every database has a collation (default is the server collation). the particular collation needs to be present on the operating system as well.
  3. Column. Overrides database level collation.
  4. Expression. Can be used to override any other collation, uses the COLLATE keyword.

--Querying the server collation:
SELECT CONVERT(char, SERVERPROPERTY('collation'))
--Querying the DB collation:
SELECT DATABASEPROPERTYEX(db_name(), 'Collation') SQLCollation
--Find Collation of a Table Column:
SELECT name, collation_name FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'History')
AND name = 'AssignmentName'

Non-Unicode to Unicode Data conversion:
  • Use unicode (n*) data type (nvarchar, nchar,  ntext) for multi-language support.
  • Defining Parameters in Stored Procedures with a Unicode data type.
  • Using the N Prefix - @name = N'Chain'.

Points to remember before Non-Unicode to Unicode Data conversion:
  • The storage size of the columns grows to twice size.
  • indexes, constraints and triggers on the columns have to be removed and recreated.
  • If your tables are large it takes its time.

  • Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales
  • Collation settings doesn't affect the data stored in columns of unicode data type, so - as long as you remember about N' prefix - collation is important only according to sort order.
  • the collation impacts data comparision tests.
  • In SQL Server 2000, you use the ntext data type instead of nvarchar(max).
  • The maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000 characters like char and varchar. In those cases, replace to nchar(max), nvarchar(max).
  • The Unicode types (distinguished by the "n" in front of the type name) uses twice as many bits (two, in fact) to store the data. So - it also need to be concerned about how much more storage you'll need with Unicode data
More useful selects:
-- Get a list of all the collations supported by SQL Server:
SELECT Name, Description FROM fn_helpcollations()

-- get not unicode columns
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ,COLUMN_NAME, DATA_TYPE ,ORDINAL_POSITION, COLUMN_DEFAULT , IS_NULLABLE
from information_schema.columns
where DATA_TYPE in ('varchar', 'char', 'text')
order by TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME


Collation Types

Get a property of a specified collation:
COLLATIONPROPERTY(collation_name,property ):
SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage') --result: 1252 


Links:
Articles:
Unicode and SQL Server, good introduction to Unicode and collation
Implementation of Unicode in SQL Server


MSDN:
International Considerations for Databases and Database Engine Applications:
Server-Side Programming with Unicode:
SQL Server Collation Names:

Forums:
Any thoughts on collation settings for an International database?

Finding Database Object Dependencies

Finding unused DB objects (Tables, Views, Procs, Functions)

Check by dependencies:

Figure out which objects have no internal dependencies:
select distinct so.id, so.name, so.Type
from sys.sysdepends sd
inner join sys.sysobjects so on so.id = sd.id
-- next line: filter by schema' if required:
inner join sys.objects o on o.object_id = so.id  and SCHEMA_NAME(o.schema_id) <> 'dbo'
where 
    not exists ( select 1 from sysdepends sd2
where sd2.depid = so.id )
and so.Type in ('AF', 'FN', 'FS', 'FT', 'IF', 'TF', 'S', 'U', 'V') -- depend which types we want to check (and if we want)

I did another checks:
1. Insert the result of this quert into table 
2. Search calls to those objects in field ROUTINE_DEFINITION in table INFORMATION_SCHEMA.ROUTINES. remove the objects that I found here.
3. Additional check in syscomments.

-- step 1
declare @UnUsedObjects TABLE (o_id int, o_name sysname, o_type char(2))

insert into @UnUsedObjects (o_id, o_name, o_type)

select distinct so.id, so.name, so.Type
from sys.sysdepends sd
inner join sys.sysobjects so on so.id = sd.id
where 
    not exists ( select 1 from sysdepends sd2
where sd2.depid = so.id )
and so.Type in ('AF', 'FN', 'FS', 'FT', 'IF', 'TF', 'S', 'U', 'V')
-- end of insert

select * from @UnUsedObjects --check after step 1

-- step 2
DECLARE @o_name nvarchar(50), @o_nameLike nvarchar(50)
DECLARE CursorWO CURSOR FORWARD_ONLY  FOR 
-------------- Start Select --------------------------
select cast(o_name as nvarchar(50)), cast('%' + o_name + '%' as nvarchar(50))
from @UnUsedObjects
-------------- End Select ----------------------------
OPEN CursorWO
FETCH NEXT FROM CursorWO 
INTO @o_name, @o_nameLike
WHILE (@@FETCH_STATUS = 0)
BEGIN
if exists (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE @o_nameLike
and SPECIFIC_NAME <> @o_name
) delete from @UnUsedObjects where o_name = @o_name
--Get next record from cursor
FETCH NEXT FROM CursorWO 
INTO @o_name, @o_nameLike
END
CLOSE CursorWO
DEALLOCATE CursorWO

select *, (CASE WHEN o_type IN ('S', 'U') THEN 10
WHEN o_type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TF') THEN 20
WHEN o_type IN ('V') THEN 30
ELSE 40
END) as ord
from @UnUsedObjects
order by ord, o_type

-- step 3
-- run this for each object:
select object_name(id) as object_name_, count(*) as count_ 
from syscomments where text like '%XXXXX%' 
group by object_name(id) order by object_name(id)

More check:
select r.routine_name, r.routine_type, o.Type, o.name as objectName
from sys.objects o
left join INFORMATION_SCHEMA.ROUTINES r on r.ROUTINE_DEFINITION LIKE '%' + o.name + '%'
             and SPECIFIC_SCHEMA <> 'dbo'  -- schema check, if required
where o.Type in ('S', 'U')
and r.routine_name is null


EXEC sp_depends @objname = N'TABLENAME'



Check by DB actual execution:
-- get last execution time:
SELECT object_name(m.object_id), MAX(qs.last_execution_time)
FROM sys.sql_modules m
LEFT JOIN (sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st)
ON m.object_id = st.objectid
AND st.dbid = db_id()
GROUP BY object_name(m.object_id)
order by MAX(qs.last_execution_time) desc

-- get accesses count of tables:
SELECT t.name AS 'Table', 
SUM(i.user_seeks + i.user_scans + i.user_lookups) AS 'Total accesses',
SUM(i.user_seeks) AS 'Seeks',
SUM(i.user_scans) AS 'Scans',
SUM(i.user_lookups) AS 'Lookups'
FROM sys.dm_db_index_usage_stats i 
RIGHT OUTER JOIN sys.tables t ON (t.object_id = i.object_id)
GROUP BY i.object_id, t.name
ORDER BY [Total accesses] desc 


Links:


Listing SQL Server Object Dependencies:

GRANT Object Permissions

GRANT SELECT ON OBJECT::Person.Address TO RosaQdM --Granting SELECT permission on a table

GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo TO Recruiting11 --Granting EXECUTE permission on a stored procedure

GRANT REFERENCES (BusinessEntityID) ON OBJECT::HumanResources.vEmployee TO Wanida WITH GRANT OPTION --Granting REFERENCES permission on a view with GRANT OPTION

GRANT SELECT ON Person.Address TO RosaQdM --Granting SELECT permission on a table without using the OBJECT phrase

GRANT SELECT ON Person.Address TO [AdventureWorks2008R2\RosaQdM] --Granting SELECT permission on a table to a domain account

GRANT EXECUTE ON dbo.uspGetBillOfMaterials TO newrole ; --Granting EXECUTE permission on a procedure to a role

Details in:

FullText Search

EXEC sp_fulltext_database 'enable' --Enabled the fulltext on the database
EXEC sp_fulltext_column 'tt', 'nn', 'add' -- tt - table, nn - column

http://amitpatriwala.wordpress.com/2008/08/11/fulltext-search-in-sqlserver-2005/

-- Enable full-text searching in the database.
EXEC sp_fulltext_database 'enable'
GO

-- Create a new full-text catalog.
EXEC sp_fulltext_catalog 'StevenBCatalog', 
                         'create' 
GO

-- Register the new table and column within it for full-text querying, 
-- then activate the table.
EXEC sp_fulltext_table 'FulltextTest', 
                       'create', 
                       'StevenBCatalog', 
                       'PK_title_id'
EXEC sp_fulltext_column 'FulltextTest', 
                        'article_title', 
                        'add'
EXEC sp_fulltext_table 'FulltextTest', 
                       'activate'
GO

-- Start full population of the full-text catalog. Note that it is
-- asynchronous, so delay must be built in if populating a
-- large index.

EXEC sp_fulltext_catalog 'StevenBCatalog', 
                         'start_full'
WHILE (SELECT fulltextcatalogproperty('StevenBCatalog',
'populatestatus')) <> 0
   BEGIN
      WAITFOR DELAY '00:00:02'     -- Check
      every 2 seconds to see if full-text index population is complete.
   CONTINUE
END

GO

-- Execute a full-text query against the new table.
SELECT article_title
FROM FulltextTest
WHERE CONTAINS(article_title, ' "Steven Buchanan" AND "ice skating" ')

SET IDENTITY_INSERT

Allows explicit values to be inserted into the identity column of a table.

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Create login and user (MSSQL)

USE [master]
GO
CREATE LOGIN [ig] WITH PASSWORD=N'ig111!', DEFAULT_DATABASE=[ItaiTest], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [ItaiTest]
GO
CREATE USER [ig] FOR LOGIN [ig]
GO
USE [ItaiTest]
GO
EXEC sp_addrolemember N'db_owner', N'ig'
GO

MSSQL Sysobjects Types

select * from sys.sysobjects
select * from sys.objects



quick copy-paste for: where type in() :

function: 'AF', 'FN', 'FS', 'FT', 'IF', 'TF'
SP: 'P', 'PC', 'RF', 'X'
view: 'V'
table: 'S', 'U'
synonym: 'SN'
constraint (& keys, etc...): 'C', 'D', 'F', 'PK', 'UQ'
trigger: 'TA', 'TR'
roles: 'R'
other: 'PG', 'SQ'

Backup and Restore DB


BACKUP DATABASE [ItaiTest]
TO DISK = N'C:\TempItai\ItaiTest.bak'
WITH NOFORMAT, NOINIT,  NAME = N'ItaiTest-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

CREATE DATABASE [ItaiTestNew]
GO

RESTORE DATABASE [ItaiTestNew]
FROM  DISK = N'C:\TempItai\ItaiTest.bak'
WITH  FILE = 1,
MOVE N'ItaiTest' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ItaiTestNew.mdf',
MOVE N'ItaiTest_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ItaiTestNew_log.ldf',
NOUNLOAD,  REPLACE,  STATS = 10
GO

Find differences between types of parameters in SPs and types of the real fields

-- Developer: Victor Shahar - All rights reserved! :-)

declare @dbname sysname
set @dbname = 'Campus'
declare @cmd varchar(max)
set @cmd = 'use ' + @dbname + ';
select distinct d.id, OBJECT_NAME(d.id) as ProcedureName, d.depid, OBJECT_NAME(d.depid) as TableName
, c.name as FieldName, t.name as FieldType, c.scale, c.prec
into #tempColumns
from sys.sysdepends d
inner join sys.sysobjects o1
on d.id = o1.id
inner join sys.sysobjects o2
on d.depid = o2.id
inner join sys.syscolumns c
on d.depid = c.id
inner join sys.systypes t
on c.xtype = t.xtype
where OBJECT_NAME(d.id) not like ''sp_%''
and o1.xtype = ''P''
and o2.xtype = ''U''

select object_name(O.id) As ProcedureName, replace(C.name,''@'','''') as InputParameter
,t.name as FieldType, c.scale, c.prec
into #tempParameters
from sys.sysobjects O
inner join sys.syscolumns C 
on C.id = O.id
inner join sys.systypes t
on c.xtype = t.xtype
where O.xtype = ''P'' 
order by 1

select tc.ProcedureName, tc.TableName,
tc.FieldName, tc.FieldType, tc.scale, tc.prec,
(''@'' + tp.InputParameter) as InputParameter,
tp.FieldType, tp.scale, tp.prec
from #tempColumns tc
inner join #tempParameters tp
on  tc.ProcedureName = tp.ProcedureName
and tc.FieldName = tp.InputParameter
where
tc.FieldType != tp.FieldType
order by 1,2,3'

exec (@cmd)

Schema - create, alter, drop

CREATE SCHEMA schema_clause [ schema_element [ , ...n ] ]

ALTER SCHEMA newschema TRANSFER oldschema.Table

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables -- /FROM sys.objects
WHERE schema_id != SCHEMA_ID('dbo')

DROP SCHEMA schema

Create a test database

--Create a test database include: login, user, role, schema, and a simple table:

--create a test database
CREATE DATABASE [SecurityTest]
GO
USE SecurityTest
GO
CREATE TABLE [dbo].[table1](
       [pkcol] [int] IDENTITY(1,1) NOT NULL,
       [col1] [int] NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO
--create test user login
CREATE LOGIN [User1] WITH PASSWORD=N'p@55w0rd'
GO
--create user in test database
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[Developer_Schema]
GO
--create role
CREATE ROLE [Developer_Role] AUTHORIZATION [dbo]
GO
--create schema
CREATE SCHEMA [Developer_Schema] AUTHORIZATION [User1]
GO
--apply permissions to schemas
GRANT ALTER ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT DELETE ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT INSERT ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT SELECT ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT UPDATE ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT REFERENCES ON SCHEMA::[dbo] TO [Developer_Role]
GO
--ensure role membership is correct
EXEC sp_addrolemember N'Developer_Role ', N'User1'
GO
--allow users to create tables in Developer_Schema
GRANT CREATE TABLE TO [Developer_Role]
GO
--Allow user to connect to database
GRANT CONNECT TO [User1]