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_
f.constraint_column_id, --object_name(f.constraint_
f.parent_object_id, object_name(f.parent_object_
f.parent_column_id, object_name(f.parent_column_
f.referenced_object_id, object_name(f.referenced_
f.referenced_column_id --, object_name(f.referenced_
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_
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.
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
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
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:
- Server.
- Database. Every database has a collation (default is the server collation). the particular collation needs to be present on the operating system as well.
- Column. Overrides database level collation.
- 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
1. Windows Collations, http://msdn.microsoft.com/en-us/library/ms188046.aspx
2. SQL Server Collations, http://msdn.microsoft.com/en-us/library/ms144260.aspx
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
select *, object_name(id) as id_name, object_name(depid) as depid_name from sys.sysdepends
sp_depends:
sys.sysdepends:
sys.sql_dependencies:
sys.sql_expression_
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
-- 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')
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'
Subscribe to:
Posts (Atom)