Posts Tagged ‘SQL Server’

SQL Server – REPLICATE function (repeat string N numbers of times)

REPLICATE function this is one of the sql server string functions. This function repeat string or char N number of time REPLICATE ( string_expression ,integer_expression ) Example. DECLARE @st VARCHAR(10)=’@1′; SELECT Replicate(@st,4) The result. @1@1@1@1

More »

Sql Server 2008 Query Designer Toolbar-Edit Data Without Update Statement

In this post i will talk about “Query Designer Toolbar“. It is one of the coolest feature that i see on SQL. First :  we will talk about how to show it. Second :    how to use it . This will show the result grid Editable (can edit the data on the flay). Now […]

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 »

Simple PowerShell Script To Retrieve Data From DataBase

As im continuing posts on PowerShell scripts . This post to demonistrate how to connect to sqlserver and retrieve data from DataBase. $connString = "data source=.;Initial catalog=Production;uid=sa;pwd=uer_password;" $QueryText = "select * from TableName" $SqlConnection = new-object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = $connString $SqlCommand = $SqlConnection.CreateCommand() $SqlCommand.CommandText = $QueryText $DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $SqlCommand $dataset = new-object System.Data.Dataset […]

More »

Drop All Stored Procedures From Your Database SQL 2008

This code sample demonstrate how to drop all stored procedures from your Database.  DECLARE @procName VARCHAR(500)       DECLARE cur cursor             FOR SELECT [name] FROM sys.objects WHERE TYPE = ‘p’       OPEN cur       fetch NEXT FROM cur INTO @procName       while […]

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 »

T-SQL Script To Drop all Stored Procedures

Early today i faced issue of deleting all stored procedure in Backup database . The normal action is to open SQL Mangement Studio and try to delete them the Problem in this solution That you can’t select all stored procedure and delete them on one click You must selecte the stored procedures one by one […]

More »

SQL SERVER–Comma Separated Values From Table Column

This T-SQL query will get value of  Table  Column in comma separated string — declare variable to hold data and  set it to null DECLARE @Countries VARCHAR(MAX)=NULL SELECT @Countries = COALESCE(@Countries + ‘,’, ”) + ”” + s.Name + ”” FROM HumanResources.Shift s SELECT @Countries — Result : ‘Day’,’Evening’,’Night’

More »