Archive for the ‘SQL Server’ 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 »

How to make fixed length digit

this is one of the easy posts for me i will talk about how to fixed length digit . means i have column stored in the DB and i want to show hime with fiexd lenght like “00001 or 00123” that is pretty much easy using one of the sql functions Called STUFF this function […]

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 »

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 »