Archive for the ‘SQL Scripts’ Category

Using information_schema to get all columns name and data types

Hi all and here we are again with one of the interesting SQL “information_schema” in this post we will talk about “information_schema.columns” it’s avery interisting view it containes all the data for the tables columns on your DataBase Here is a list of the column in “information_schema.columns” TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_CATALOG,CHARACTER_SET_SCHEMA CHARACTER_SET_NAME,COLLATION_CATALOG,COLLATION_SCHEMA,COLLATION_NAME DOMAIN_CATALOG,DOMAIN_SCHEMA,DOMAIN_NAME and now […]

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 »

SQL Query To Determine The Last Database Backup Date

This simple script will get list of dates of backups. USE msdb SELECT     database_creation_date,backup_start_date,backup_finish_date,            name, user_name, backup_size FROM         backupset WHERE     (database_name = N’DataBaseName’) ORDER BY backup_finish_date DESC;

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 »

SQL SERVER – Using Group by With Rollup (Transact-SQL)

Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row. The number of groupings that is returned equals the number of expressions in the plus one. For example, Consider the Following statement. SELECT a, b, c, SUM (EXPRESSION) FROM T GROUP BY ROLLUP; Here is an example. […]

More »

SqlServer-Delete Duplicate Records Rows

This is the second post in removing data from a table or view. In this post we will discuss how to remove duplicate rows. In the testing or even production phase of application we fiend that some data repeated or inserted more than once. This can be very annoying and causes miss behavior of the […]

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 »