Migrer une base de données vers un nouveau serveur est assez simple… si… Il y a seulement une base de données; Il n’y a pas des objets à migrer; Il n’y a pas de dépendances physiques; Alors, le SQL script proposé fait une partie du processus : Ferme toutes les connexions vers la base de données à migrer; Restreins...
Microsoft provides system stored procedures (dbo.sp_changedbowner) for changing the db owner. Keep in mind, a user database should have a db owner associated with it; dont mis interpret this witj db_owner role. I personally prefer setting the db owner to ‘sa’ 1 2SELECT NAME, 3 suser_sname(owner_sid) AS 'owner', 4 CASE...
Set database owner = ‘sa’ Set RECOVERY = Simple & Shrink databases 1 2print '-- SQL Script to fix owner and Recouvery mode' 3print '' 4SET NOCOUNT ON; 5Declare @sql nvarchar(max); 6declare @dbname nvarchar(255); 7BEGIN TRY drop table #db; END TRY BEGIN CATCH END CATCH; 8SELECT * INTO #db 9FROM sys.databases f 10WHERE...
Create SQL Script to create SQL Script to backup databases and to restore databases. - Fix owner user : ‘sa’ - Shrink databases Tip: You can use a shared folder; Tip: You can disconnect all users before to restore a database; Tip: You can add this SQL Script to protect executing on the wrong SQL Server; 1 2IF...
1 2go 3-- Last SQL Server instance restart. 4SELECT sqlserver_start_time FROM sys.dm_os_sys_info; 5go 6-- Last SQL Agent Server restart. 7SELECT login_time as 'sqlserver_agent_start_time' FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher'; 8go 9-- Last DB windows server restart. 10exec...
We can spawn a Windows command shell and passes in a string for execution. Any output is returned as rows of text. (msdn) ex: 1 2xp_cmdshell 'dir C:\' Set configuration 1 2-- -------------------------------- 3-- Set Configuration 4-- -------------------------------- 5-- 6 7-- To allow advanced options to be changed....
This article is nice ( we have a little bug ) , and the SQL script is not execute-safe (if you run twice, you have errors ). It is for a specific database, when you need to save all SQL Server, you must run for each database. 1 2PRINT '-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --' 3PRINT '-- SQL Security...
Script to create script to copy data from two datatable when tables are equal. This code is very useful when partially we save data.We can delete data after some date but we must have a column date. Read the conditions to run this script. 1 2------------------------------- 3-- Script to create script to copy datatable...
Transform table definition to XML select, it can be useful when we need to create many XML select or re-create it. Result: 1 2SELECT 3 [Message].value('(msg_Customers/Customers/CustomerID)[1]', 'nchar(5)') as [CustomerID] 4, [Message].value('(msg_Customers/Customers/CompanyName)[1]', 'nvarchar(40)') as [CompanyName] 5,...
We have many ways to obtain the size of all tables using SQL Server. This is a nice solution but we do not really need to use temporary table. http://therightstuff.de/2007/11/19/How-To-Obtain-The-Size-Of-All-Tables-In-A-SQL-Server-Database.aspx If we run the script twice, we have an error. Well, this version uses a...
We have a lot of articles about how to do but most of the time it is too much information. This article tries to answer 2 questions! How can we create XML column from a SQL query? How can we create a table from XML column? This example use NorthWind database: https://northwinddatabase.codeplex.com/releases/view/71634 1...
Créer le même code à adapter à chaque table selon les champs et le type. Cela peut être une activité assez plate. On peut créer un code qui crée notre code. Peut-on appeler une métacreation, ou métacodification, mais, peut-être, il n’existe pas ce mot en français. Alors, ce SQL script nous liste les champs de tables,...
SQL Server does not help to easily determine the dependencies between tables. This research presents the parent table and the child tables and columns used. https://msdn.microsoft.com/en-us/library/ms179610.aspx http://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server well, however,...
Après restaure la base de données MS SQL, il est possible que certains utilisateurs de la base de données soient orphelins. Alors, voici les SQL scripts pour les réparer. Répare tous les utilisateurs orphelins de toutes bases de données: 1 2DECLARE @sql as nvarchar(max); 3SET @sql = ' 4USE[?];PRINT''Database:...
Sometimes I want to have control over a SQL Server database, but always some connection blocks. We can use this 1 2USE MASTER; 3ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; BUT, sometime, we cannot do it! Well, we have this solution… 1 2SET NOCOUNT ON; 3DECLARE @ToKill AS NVARCHAR(max); 4SET...
How convert a text to a Title text with TSQL. It’s “easy” solution. 1 2CREATE FUNCTION udf_TitleCase (@InputString VARCHAR(4000)) 3RETURNS VARCHAR(4000) 4AS 5BEGIN 6 DECLARE @Index INT 7 DECLARE @Char CHAR(1) 8 DECLARE @OutputString VARCHAR(255) 9 10 SET @OutputString = LOWER(@InputString) 11 SET @Index = 2 12 SET...
How can we send email by sql script ? To enable Database Mail XP 1 2USE msdb; 3GO 4EXEC sp_configure 'Database Mail XPs' 5GO 6RECONFIGURE 7GO 8EXEC sp_configure 'Database Mail XPs', 1 9GO 10RECONFIGURE 11GO 12EXEC sp_configure 'Database Mail XPs' 13GO To configure database mail 1 2USE msdb 3GO 4 5DECLARE @ProfileName...
On a besoin *parfois* de connaitre ou avoir la liste de toutes les tables et les colonnes Ce script crée une table avec toutes définitions. ex: 1 2select * from _text_table_dictionary 3where Datatype = 'datetime' 1 2PRINT @@servername; 3GO 4 5IF EXISTS ( 6 SELECT * 7 FROM dbo.sysobjects 8 WHERE id =...