SQL SCRIPT GENERATOR

If you want to create Database Scripts, Object Scripts or  insert statements of  SQL server database, download this file SQL SCRIPT GENERATOR and convert its extenstion to *.rar and then extract it

go to bin’s folder and execute “GenDBScripts.exe” and enjoy 🙂

Advertisements

Change ownership – SQL server

DB TAbLES
=========
DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
  @old = ‘oldOwner_CHANGE_THIS’
  , @new = ‘dbo’
  , @sql = ‘
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+”.”+QUOTENAME(TABLE_NAME) = ”?”
      AND TABLE_SCHEMA = ”’ + @old + ”’
  )
  EXECUTE sp_changeobjectowner ”?”, ”’ + @new + ””

EXECUTE sp_MSforeachtable @sql

STORED PROCEDURES
=================
DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
@old = ‘oldOwner’
, @new = ‘dbo’
, @sql = ‘
IF EXISTS (SELECT NULL FROM information_schema.routines
WHERE
QUOTENAME(ROUTINE_SCHEMA)+”.”+QUOTENAME(ROUTINE_NAME) = ”?”
AND ROUTINE_SCHEMA = ”’ + @old + ”’
)
EXECUTE sp_changeobjectowner ”?”, ”’ + @new + ””

EXECUTE sp_MSforeachtable @sql

———————————————————————————–

CAST and CONVERT (T-SQL)

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax

Using CAST:

CAST(expression AS data_type)

Using CONVERT:

CONVERT (data_type[(length)], expression [, style])

Arguments
expression
Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.
data_type
Is the target system-supplied data type. User-defined data types cannot be used. For more information about available data types, see Data Types.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
style
Is the style of date format you want when converting datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

 

Without
century
(yy)
With
century
(yyyy)

Standard

Input/Output**


Important By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use a cutoff year of 2030. SQL Server provides a configuration option (two digit year cutoff) that changes the cutoff year used by SQL Server and allows the consistent treatment of dates. The safest course, however, is to specify four-digit years.


When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar data type length.

This table shows the style values for float or real conversion to character data.

 

Value Output

In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.

 

Value Output
Return Types

Returns the same value as data type 0.

Remarks

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified. This chart shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types.


Note Because Unicode data always use an even number of bytes, use caution when converting binary or varbinary to or from Unicode supported data types. For example, this conversion does not return a hexadecimal value of 41, but of 4100:


SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)


Automatic data type conversion is not supported for the text and image data types. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length you can specify is 8000. If you attempt a conversion that is not possible (for example, if you convert a character expression that includes letters to an int), SQL Server generates an error message.

When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table.

From data type To data type Result

Microsoft SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from release to release. This example shows such a roundtrip conversion:

DECLARE @myval decimal (5, 2)

SET @myval = 193.57

SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))

-- Or, using CONVERT

SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

 

Do not attempt to construct, for example, binary values and convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between releases of SQL Server.

When data types are converted with a different number of decimal places, the value is truncated to the most precise digit. For example, the result of SELECT CAST(10.6496 AS int) is 10.

This example shows a resulting expression that is too small to display.

USE pubs

SELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))

FROM titles

WHERE type = 'trad_cook'

  

Here is the result set:

Title                       

------------------------- --

Onions, Leeks, and Garlic * 

Fifty Years in Buckingham * 

Sushi, Anyone?            * 

  

(3 row(s) affected)

  

When data types in which the target data type has fewer decimal points than the source data type are converted, the value is rounded. For example, the result of CAST(10.3496 AS money) is $10.35.

SQL Server returns an error message when char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal; or when an empty string (“ “) is converted to int or float.

Using Binary String Data

When binary or varbinary data is converted to character data and an odd number of values is specified following the x, SQL Server adds a 0 (zero) after the x to make an even number of values.

Binary data consists of the characters 0 through 9 and A through F (or through f), in groups of two characters each. Binary strings must be preceded by 0x. For example, to input FF, type 0xFF. The maximum value is a binary value of 8000 bytes, each of which is FF. The binary data types are not for hexadecimal data, but rather for bit patterns. Conversions and calculations of hexadecimal numbers stored as binary data can be unreliable.

When specifying the length of a binary data type, every two characters count as one. A length of 10 signifies that 10 two-character groupings will be entered.

Empty binary strings, represented by 0x, can be stored as binary data.

Examples
A. Use both CAST and CONVERT

Each example retrieves the titles for those books that have a 3 in the first digit of year-to-date sales, and converts their ytd_sales to char(20).

-- Use CAST.

USE pubs

GO

SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales

FROM titles

WHERE CAST(ytd_sales AS char(20)) LIKE '3%'

GO

  

-- Use CONVERT.

USE pubs

GO

SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales

FROM titles

WHERE CONVERT(char(20), ytd_sales) LIKE '3%'

GO

  

Here is the result set for either query:

Title                          ytd_sales  

------------------------------ -----------

Cooking with Computers: Surrep 3876       

Computer Phobic AND Non-Phobic 375        

Emotional Security: A New Algo 3336       

Onions, Leeks, and Garlic: Coo 375        

  

(4 row(s) affected)

  

B. Use CAST with arithmetic operators

This example calculates a single column computation (Copies) by dividing the total year-to-date sales (ytd_sales) divided by the individual book price (price). This result is converted to an int data type after being rounded to the nearest whole number.

USE pubs

GO

SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'

FROM titles

GO

  

Here is the result set:

Copies     

-----------

205        

324        

6262       

205        

102        

7440       

NULL     

383        

205        

NULL     

17         

187        

16         

204        

418        

18         

1263       

273        

  

(18 row(s) affected)

  

C. Use CAST to concatenate

This example concatenates noncharacter, nonbinary expressions using the CAST data type conversion function.

USE pubs

GO

SELECT 'The price is ' + CAST(price AS varchar(12))

FROM titles

WHERE price > 10.00

GO

  

Here is the result set:

-------------------------

The price is 19.99       

The price is 11.95       

The price is 19.99       

The price is 19.99       

The price is 22.95       

The price is 20.00       

The price is 21.59       

The price is 10.95       

The price is 19.99       

The price is 20.95       

The price is 11.95       

The price is 14.99       

  

(12 row(s) affected)

  

D. Use CAST for more readable text

This example uses CAST in the select list to convert the title column to a char(50) column so the results are more readable.

USE pubs

GO

SELECT CAST(title AS char(50)), ytd_sales

  

FROM titles

WHERE type = 'trad_cook'

GO

  

Here is the result set:

                                                       ytd_sales

--------------------------------------------------     ---------

Onions, Leeks, and Garlic: Cooking Secrets of the      375

Fifty Years in Buckingham Palace Kitchens              15096

Sushi, Anyone?                                         4095

  

(3 row(s) affected)

  

E. Use CAST with LIKE clause

This example converts an int column (the ytd_sales column) to a char(20) column so that it can be used with the LIKE clause.

USE pubs

GO

SELECT title, ytd_sales

FROM titles

WHERE CAST(ytd_sales AS char(20)) LIKE '15%'

    AND type = 'trad_cook'

GO

  

Here is the result set:

title                                                        ytd_sales  

------------------------------------------------------------ -----------

Fifty Years in Buckingham Palace Kitchens                    15096      

  

(1 row(s) affected)

Database Schema online

WWW SQL Designer

Actual Site: http://ondras.zarovi.cz/sql/
Download software link : http://ondras.zarovi.cz/sql/sql-1.4.zip

http://ondras.zarovi.cz/sql/demo/
http://www.shdev.de/sqldesigner/index.php?keyword=ContactR

Design your schema online and you can even access to your previously defined schema.

With this tool you can see the current DB Design and even Modify it. So if you have any proposals for improving the database design or find some major flaws you can post it as a comment here or just draw a picture with the tool 😉

Please save it under a new keyword if you modify it so I can see all the different proposals. If some of you just don’t care and overwrite it anyways, you others dont fear:

If there are some problems (like someone deleted or overwrote the Schema) or the Database has changed but you still want to see or improve the Database Design, here is a XML file with which you can recreate this version. Just click Import from XML in the SQL Designer and paste the XML data.

Don’t worry no matter what you do you won’t affect the real database in any way.

 Snapshot of the Database

Main SqlServer Helping link

http://www.sql-server-helper.com/

Extended DateFormats (SQLserver)

Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] 99-01-24
YYYY-MM-DD SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] 1999-01-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08
Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 2006 1
Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1
Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1
DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month] 11 September 1
Month DD 1 SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1
DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1
DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
12/92
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
92/12
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) AS [MMDDYY] 122506
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) AS [MMDDYYYY] 12252006
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) AS [DDMMYY] 240702
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) AS [DDMMYYYY] 24072002
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY] Sep-02 1
Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY] Sep-2002 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY] 25-Dec-05 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY] 25-Dec-2005 1

1 To make the month name in upper case, simply use the UPPER string function. 

DateFormats – SQLSERVER (standard)

Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM