Posts Tagged ‘TSQL’

TSQL – Concatenate Rows Into On Column Using For XML Path()

This case always happen when you are working  with large business that you need Concatenate rows into one string . one of the methods to do that is to Use XML Path() to turn rows into one XML with no tags. As follows 1)  Declare To Tables With one to many relation DECLARE @Categories TABLE ( CategoryID INT, Category_Code […]

More »

T-SQL script to backup all user databases with date time backup name

This simple script to backup all user databases to specific location . USE master; GO DECLARE @DATE VARCHAR(25) SELECT @DATE= REPLACE( CONVERT(VARCHAR, GETDATE(),111),’/’,’:’)+’ ‘+ CONVERT(VARCHAR, GETDATE(),114); DECLARE DB_Cursor Cursor FOR   — Get all user databases SELECT name AS DatabaseName FROM sys.sysdatabases WHERE ([dbid] > 4)   OPEN DB_Cursor DECLARE @DataBaseName VARCHAR(100); DECLARE @backupPath nvarchar(100); […]

More »

Clean Up Your Database From Malware

If your site was hacked and all you database data has been changed to hold MalWare script. I have made a simple script to clean you data from this text. DECLARE @col sysname DECLARE @tbl sysname DECLARE @SQL nvarchar(256) DECLARE crsFix cursor FOR SELECT TABLE_NAME, Column_name FROM information_schema.COLUMNS INNER JOIN sys.TABLES ON sys.TABLES.name=information_schema.COLUMNS.TABLE_NAME WHERE (data_type […]

More »

Moving sys DataBases Recreate missing TempDB

Simply to move your database files you can use script to change file location for any database. And to make sure that the file path are changed run this script. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>’); Restart your instance and all done. Now additionla scenarios Moving TempDB If you […]

More »

Delete Statment in SQL

DELETE (Transact-SQL) Removes rows from a table or view. in this post i will start series about deleting data from Table Or View. i will discuse and show simple and complex delete statments A. Using DELETE with no WHERE clause USE AdventureWorks2008R2; GO DELETE FROM Sales.SalesPersonQuotaHistory; GO B. Using DELETE on a set of rows […]

More »

How To Get Column Name Only Without Data In SQL Server

In my daily work I always needs to know Table Structure or Column Names . I usually use “sp_help TableName” To Get alot of information about table. now i will introduce another Way to do That. SET FMTONLY off SELECT * FROM TABLE — This In The Top of your Query Will Get Only Column […]

More »