Archive for the ‘SQL Query’ 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 Diagnostic Information Queries

how to get information for current SQL instance and OS Version SELECT @@VERSION AS [SQL Version Info];   —   2008 RTM Builds                    2008 SP1 Builds — Build       Description        Build        Description — 1600        Gold […]

More »

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 »

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 »

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 »

Deadlock When Deleting by Foreign Key

Hi all in this post i will talk about a problem i suffered from for along time it’s Deadlock when deleting from a table by value of Foreign . i always get the error “Transaction (Process ID n) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun […]

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 »