Executing a T-SQL batch multiple times using GO

Executing a T-SQL batch multiple times using GO

http://www.mssqltips.com/tip.asp?tip=1216

Search all columns in all the tables in a database for a specific value

Search all columns in all the tables in a database for a specific value:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

SQL Naming & Coding Convention Standards

Valid Characters
Alphanumeric characters only ([a-zA-Z0-9]) – In every case only alphanumeric characters (and some situations underscore) are allowed
General Rules
The following rules should apply to all database objects.
• Clear and unambiguous naming.
o The name should provide as much information as possible about an object and provide an obvious connection to references in project documentation. Choice of names should typically consider a name space much larger than a project—preferably the enterprise. A name uniquely identifies a database object; its definition should be consistent wherever the object is used. What might seem clear in a narrow context may lose meaning outside of that context. Saving keystrokes should never be used as a criterion in selecting a name.
• Abbreviations should be avoided.
o Only when length restrictions apply should they be permitted. When used, abbreviations should follow the abbreviation rules.
o Example :
--Correct
DECLARE @Counter int
--Avoid
DECLARE @C int
• Alphanumeric characters only ([a-zA-Z0-9]) – Do not use special characters; restrict names to alphanumeric characters, no space is allowed in object name:
o Example :
--Avoid
CREATE TABLE dbo.[User Information]

• System naming should be avoided. Do not use any prefix or object names that somehow links to system object. No DB object can be created under “SYS” schema and there are limitations on prefixes on Stored procedures too, Please check Stored Procedure section for more details.
• Use Upper case for all T-SQL constructs (except types).
o Example : SELECT MAX(MyField) FROM MyTable
• Use Lower case for all T-SQL types and usernames:
o Example : DECLARE @MyVariable int



Database Object Naming Rules
Summary:
Object Prefix Suffix Alpha Numeric Characters Notes
Tables x Use singular form: Example User, not Users
Linking Tables _Link x Formed from the Tables they are linking, Example: A Table joining User and Group would be UserGroupLink
Table Columns x
Primary Key PK_ x
Clustered Index IXC_ x
Unique Clustered Index IXCU_ x
Unique Index IXU_ x
Index IX_ x
XML Index XML_IX_ x
XML Columns x Use .net casing, no underscores
Constraints CK_ x
Default Value DF_ x
Foreign Keys FK_ x
Views VW_ x
Functions FN_ x
Stored Procedures PR_ x
Triggers (after) TRGA_ x
Triggers (instead) TRGI_ x
CTE (Common Table Expressions) CTE_ x
Schemas
• Use lowercase for schema names.
• Always alias database objects using the schema name, even if this is the default [dbo] schema
• This applies to both CREATE statements and when referencing objects in FROM, INSERT or UPDATE statements etc.
Table Names
• Prefixes are used to identify the table with relation to application modules as much as possible. Some of the most common prefixes are as :
• ANALYTICS_
• AUDIT_
• BILLING_
• CAREER_
• CERT_
• CMP_
• COMMENT_
• COMPASSES_
• CONNECT_
• CORP_
• DESIGNATION_
• DEVPLAN_
• EMAIL_
• EVALUATION_
• LCMS_
• LICENSE_
• LO_
• OU_
• PO_
• PROXY_
• QTI_
• REPORT_
• RESUME_
• REVIEW_
• SECURITY_
• SMP_
• SOAP_
• TALENT_
• TASK_
• USER_
• WAREHOUSE_

• Alphanumeric characters only
• Always assign schema name to UDO’s (User Define Object) when defining or referencing
• Example :
--Correct
CREATE TABLE dbo.MyTable (...)
--Avoid
CREATE TABLE MyTable (...)

• Use the Singular Form Example : User, not Users
• Example :
--Correct
CREATE TABLE dbo.Address
--Avoid
CREATE TABLE dbo.Addresses

--Correct
SELECT * FROM dbo.MyTable (...)
--Avoid
SELECT * FROM MyTable (...)

Linking Table Names
• Linking Tables should be the name of the two tables it is joining, suffixed with _Link. Eg a joining table on User and Group would be User_Group_Link
Column Names
• Alpha-numeric
• No Prefix
• Format:
use the following components in the order below;
o Table Name: Primary keys only; Tables names are used to prefix all columns in dotted format, so this is not necessarily. The exception is the primary key since this is used in foreign keys.
o Qualifier: Optional; Description, to clarify the meaning of the field. For example, if a product has two images, this would clarify the field, eg. FrontImage and RearImage
o Name: Required; This is a database independent “datatype” descriptor which is used to classify the type of data. Below is a common list of standard classifiers. The exception to this is a Boolean. This should be Prefixed with “Is” as this more positively represents the meaning o the value. Flag suffix is considered optional “Flag” or Example. IsEnabled or IsEnabledFlag

Classifier Description Suggested SQL Data Type
Address Street or mailing address data nvarchar
Age Chronological age in years int
Average Average; consider a computed column numeric
Amount Currency amount money
Code Non Database Identifier
Count
Data A field containing extensible data xml
Date Calendar date smalldatetime
Datetime Date including time datetime
Day Day of month (1 - 31) tinyint
Description Brief narrative description nvarchar(MAX)
Duration Length of time, eg minutes int
ID Unique identifier for something int
Image A graphic image, such as a bitmap varbinary(MAX)
Flag Not Required: Flag indicates a boolean indicator, where the Qualifier verb does not make it clear it is a verb. Examples of a Qualifier are: Is, Has, Uses. Eg IsEnabled bit
Month Month of year
Name Formal name nvarchar
Number
Percent Number expressed as a percent
Quantity A number of things any numerical
Rate Number expressed as a rate any numerical
Ratio A proportion, or expression of relationship in quantity, size, amount, etc. between two things any numerical
Sequence A numeric order field int
Text Freeform textual information nvarchar(MAX)
Time Time of day smalldatetime
Title Formal name of something nvarchar
Version Timestamp timestamp
Weight Weight measurement any numerical
XML A field containing xml data xml
Year Calendar year or julian year number
Stored Procedure Names
• Naming Format: use the following components in the order below;
o Object: Required; usually the table or combinations of tables it is affecting, followed by underscore.
o Action: Required; eg Save, Load, Get, Set, SetSingle, Search, Delete
o Qualifier: Optional; additional descriptive words which help to clarify the specific meaning of the stored procedure
o Return Type: Optional; Indicates the type of data return
• Example Stored Procedure Names:
o pr_cmp_adjustment_save
o pr_cmp_adjustment_delete
o pr_cmp_adjustment_get
o pr_cmp_adjustment_search
• Do not:
o Use special characters.
o Use stored procedure group numbers (e.g. myProc;1).
o Prefix names with “dt_” or “sp_” or “xp_” as those are reserved for procedures shipped by SQL Server.
• Always create scope when defining Procedures and multi statement functions.
o Example :
--Correct
CREATE PROCEDURE dbo.uspMyProcedure
AS
BEGIN
(...)
END
--Avoid
CREATE PROCEDURE dbo.uspMyProcedure
AS
(...)
• When joining table always identify all columns with aliases and always alias the AS keyword.
o Example :
--Correct
SELECT U.Surname,
A.Street
FROM dbo.[User] AS U
JOIN dbo.Address AS A ON U.AddressID = A.AddressID
--Avoid
SELECT U.Surname,
Street –-Missing alias
FROM Users U –-Missing AS
JOIN dbo.Address ON U.AddressID = dbo.Address.AddressID
• When defining procedures and functions, include a commented Test Harness. Declare used variables for usage in testing:
o Example :
--Correct
CREATE FUNCTION dbo.tvfMyFunction
(
@MyParameter int
)
AS
/* TEST HARNESS
DECLARE @MyParameter int
SET @MyParameter = 1
SELECT * FROM dbo.tvfMyFunction(@MyParameter)
*/
(...)
--Correct
CREATE PROCEDURE dbo.uspMyProcedure
(
@MyParameter int
)
AS
/* TEST HARNESS
DECLARE @MyParameter int
SET @MyParameter = 1
BEGIN TRAN
SELECT * FROM dbo.MyTable –-MyTable before operation
EXEC dbo.uspMyProcedure(@MyParameter)
SELECT * FROM dbo.MyTable –-MyTable after operation
ROLLBACK TRAN
*/
(...)
--Avoid
/* TEST HARNESS
SELECT * FROM dbo.tvfMyFunction(1) –-argument not declared
*/

• Always individually name fields in INSERT and UPDATE statements. Never use the * operator in such statements.
o Example :
--Correct
INSERT INTO dbo.[User] (FirstName, LastName)
VALUES (@FirstName, @LastName)
--Avoid
INSERT INTO dbo.[User]
VALUES (@FirstName, @LastName)

• Set the NOCOUNT state as the first statement in all Procedures where you don’t specifically need the count returned.
o Example :
--Correct
CREATE PROCEDURE dbo.uspMyProcedure (...)
AS
BEGIN
SET NOCOUNT ON
(...)
• Set ISOLATION LEVEL READ UNCOMMITTED for retrieving data procs. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
o Example :
--Correct
Select FirstName, LastName FROM dbo.[User] With (Nolock)
--Avoid
Select * FROM dbo.[User]
User Defined Functions (UDF) Names
• Naming Format: use the following components in the order below;
o Prefix: Required; “FN_”
o Object: Required; usually the table or combinations of tables it is affecting, followed by underscore.
o Action: Required; eg Get, Set, SetSingle, Search, Delete
o Qualifier: Optional; additional descriptive words which help to clarify the specific meaning of the stored procedure
o Return Type: Optional; Indicates the type of data return
• Example Function Names:
o fn_get_credential_name
Parameters and Parameters Type - Stored Procedure/UDFs
 Example : Decalre @PageID int
 Data types should be lowercase, like “int”, “nvarchar(100)”
Variables - Stored Procedure/UDFs
 Example : Decalre @PageID int
 Data types should be lowercase, like “int”, “nvarchar(100)”
Recursive (CTE or while loop)
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix with “CTE_”
o Object: Required; usually the table being iterated over.
o Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata
• Example :
WITH CTE_Sales (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN CTE_Sales AS OS ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN CTE_Sales AS OM ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

• Note: Avoid the use of cursors where possible. Instead use a while loop
Updatable / Non Updatable View Names
For Views which are updatable, act as if they are a table.
This holds true for Updatable Partitioned Views.
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix with “VW_”
o Object: Required; usually related to the table(s) affected by the view
o Qualifier: Optional; additional descriptive words which help to clarify the purpose of the view.
o Example View Names are :
• vw_report_user_real_time
• vw_transcript_history
Trigger Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix with “TRG”
o Type: Required; depending on type of trigger, after or instead of. prefix with “A_” or “I_”
o Object: Required; usually the table being iterated over.
o Actions covered: Required; composite key of actions, “Upd”, “Ins”, “Del”
• Example Trigger Names:
o TRGA_CustomerInsUpdDe
o TRGA_ProductDel
o TRGI_AuthorUpd
• Note: Avoid the use of triggers where possible.
Primary Key Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix primary key with “PK_”
o TableName: Required; Table name of table being keyed
• Examples:
o PK_users
Index Names
Index names are unique within a table so it isn’t necessary to include the tablename in the index. When looking at execution plans it is helpful to have a hint about the columns being indexed
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix with “IX”
o Clustered: Required; if Clustered Index include “C”
o Unique: Required; if Unique Index include “U”
o Column Names: Required; Include the list of columns indexed, using underscores between the column names. For an index that covers all columns in the table, use the word All.
• Example Index Names:
o IXCU_user_ref (clustered unique)
o IXU_user_mgr (unique)
o IX_users_name_last_name_first (composite index)
o IXC_users_guid (clustered not unique)
Foreign Key Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix foreign key with “FK_”
o Reference Table Name(s): Required; Table name of table in the join, for which a unique index is on column(s) being linked. Where both have a unique index, such as linking key, order is optional
o Foreign Table Name(s): Required; Table name of table in the join, for there is not a unique index on the column(s) being linked.
• Example foreign key names:
o FK_users_user_type
o FK_users_users_status_local
Default Value Constraint Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix default value constraint with “DF_”
o TableName: Required; Table name
o ColumnName: Required; Column name
• Example foreign key names:
o DF_users_absent
Check Constraint Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix check constraint with “CK_”
o TableName: Required; Table name
o Integer: Required; Where the integer id is used to distinguish the check constraint from other check constraints on the same table.
• Example foreign key names:
o CK_users1

Getting started with SQL Azure

Getting started with SQL Azure

http://www.sqlservercentral.com/articles/SQL+Azure/68333/

Insert into a table with identity column

this is an easy way to insert into a table with default valus that has a identoty column.

this sample will insert 500 records.


WHILE SCOPE_IDENTITY()<500 OR SCOPE_IDENTITY() IS NULL INSERT INTO test1 default VALUES

Collect DB sizes across all Servers

drop table #tmp
create table #tmp (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),)

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
insert into #tmp
exec sp_helpfile
--exec sp_spaceused
end
'
go

select * From #tmp

---------------------------------------------
--ANOTHER METHOD
---------------------------------------------


drop table #tmp
create table #tmp (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),db nvarchar(100))
drop table #tmpfinal
create table #tmpfinal (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),db nvarchar(100))

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
insert into #tmp (name,fileid,filename,filegroup,size,maxsize,growth,usage)
exec sp_helpfile
--exec sp_spaceused
insert into #tmpfinal (name,fileid,filename,filegroup,size,maxsize,growth,usage,db)
select name,fileid,filename,filegroup,size,maxsize,growth,usage,''?'' from #tmp
truncate table #tmp
end
'

select * From #tmpfinal where filename like '%ftrow_ft_modules.ndf%'

Find a string in Entire Server's Stored Procedures

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
select ''?'' as DB, o.name, c.[text] --,*
from dbo.sysobjects o inner join dbo.syscomments c on o.[id] = c.[id] where c.[text] like ''%MemberSite_Current%''
end
'
go

Drop User, Drop Owned Schema, Create User, Add RoleMember

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''IF EXISTS (SELECT * FROM sys.schemas WHERE name = N''''SPARKSTAGE\developers'''')''
print ''DROP SCHEMA [SPARKSTAGE\developers]''
print ''go''
print ''IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''''SPARKSTAGE\developers'''')''
print ''DROP USER [SPARKSTAGE\developers]''
print ''go''
print ''CREATE USER [SPARKSTAGE\developers] FOR LOGIN [SPARKSTAGE\developers]''
print ''go''
print ''EXEC sp_addrolemember N''''db_datareader'''', N''''SPARKSTAGE\developers''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_datawriter'''', N''''SPARKSTAGE\developers''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_executor'''', N''''SPARKSTAGE\developers''''''
print ''go''
end
'
go

Writting into Win Application log from T-SQL

declare @msg varchar(1000)
set @msg = 'LADBREPORT.mnSubscription.dbo.MemberTran table is missing records'
RAISERROR (@Msg, 16, 1) WITH LOG --> this is Error
exec xp_logevent 60000,@Msg, WARNING --> this is Warning

GRANT EXECUTE ON StoredProcedures TO Account

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)''
print ''set @user = ''''db_executor''''''
print ''DECLARE Cur_name CURSOR FOR ''
print ''select specific_name From information_Schema.routines''
print ''OPEN Cur_name''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''WHILE @@FETCH_STATUS = 0''
print ''BEGIN''
print ''set @string =''''GRANT EXECUTE ON [dbo].[''''+@name+''''] TO [''''+@user+'''']''''''
print ''print @string''
print ''exec sp_executesql @string''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''END''
print ''CLOSE Cur_name''
print ''DEALLOCATE Cur_name''
print ''go''
end
'
go