Archive for the ‘tips & tricks’ 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 »

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 – 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 »

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 »

Get the next value of identity column

We are as programmers it is used to make Primary Key for table as Identity. Now if we want to pre know the next value that will be assigned when inserting new row, We can achieve that by calling SQL Function “IDENT_CURRENT” that get the current Value for the Primary Identity Column. SELECT IDENT_CURRENT (’dbo.TableName’) […]

More »

Delete All Data From Your DataBase SQL

Here is a simple and efficient code to empty DataBase from data, using one of the non documented SQL functions “sp_MSFOREACHTABLE”. Using This code CREATE PROCEDURE dbo.[sp_DeleteAllData] AS EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’ EXEC sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’ EXEC sp_MSForEachTable ‘DELETE FROM ?’ EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK […]

More »