Category Archives: SQL

Transact SQL useful functions to mass database change

a list of useful functions to rename or massively create tables in a Microsoft SQL sever database (Transact-SQL)

–-Create scripts for All Procs
SELECT SM.definition
FROM sys.sql_modules SM
INNER JOIN sys.Objects SO
ON SM.Object_id = SO.Object_id
WHERE SO.type = ‘p’
 
-–Create scripts for All Views
SELECT SM.definition
FROM sys.sql_modules SM
INNER JOIN sys.Objects SO
ON SM.Object_id = SO.Object_id
WHERE SO.type = ‘v’
   
-–Create scripts for All Functions
SELECT SM.definition
FROM sys.sql_modules SM
INNER JOIN sys.Objects SO
ON SM.Object_id = SO.Object_id
WHERE SO.type = ‘FN’
Facebooktwittergoogle_plusredditpinterestlinkedinmail

Read More ...

SQL custom backup

A very useful script to generate a backup file in SQL server by day of week and not have to create a maintenance plan.

declare @a as nvarchar(200)
set @a=N'\\backupserver\backups\ddbb' + DATENAME(WEEKDAY, GETDATE()) + '.tbk'
BACKUP DATABASE [bbdd] TO  DISK =@a WITH NOFORMAT, INIT,  NAME ='copia', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
Facebooktwittergoogle_plusredditpinterestlinkedinmail

Read More ...

T-SQL single quote by accent

Many times the use of single quote in the fields of the database gives us headaches, a simple solution is to use the thus selects:

SELECT REPLACE(nombre,””,’´’) as nombresincomilla,nombre FROM tabla

In this example we see that the result in a column to replace the single quote by the accent, avoiding problems
Another solution is to replace the fields with the single quote by accent:

UPDATE tabla SET nombre= REPLACE(nombre,””,’´’) WHERE nombre LIKE ‘%”%’

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Read More ...

Categories

Subscribe to my Newsletter