Find Database size in SQL-Server

Find Database size in SQL-Server

Note: user must be DBOwner to execute this Procedure.

 

sp_helpdb  will give all databases in the sql-server instance
and their sizes ,

Name of the Database: pubs

DB size in MB/GB

database ID <=> dbid

Created Date.

Status online/offline

compability_level:  110 --> means SQL Server 2012.

 

name    db_size    owner    dbid    created    status    compatibility_level
AdventureWorks2012        205.75 MB    machine-target\Administrator    7    Oct 26 2012    NULL    110
EntLibQuickStarts          5.08 MB    sa    11    Nov  5 2012    NULL    110
LibMgmt          7.00 MB    sa    13    Nov 12 2012    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706,  IsFullTextEnabled    110
master          6.63 MB    sa    1    Apr  8 2003    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706,    110
model          5.31 MB    sa    3    Apr  8 2003    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706,    110
msdb         21.25 MB    sa    4    Feb 10 2012    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706,  IsFullTextEnabled    110
MyOrderDatabase          5.38 MB    machine-target\Administrator    18    Jan  3 2013    NULL    110
nopcommerce         10.38 MB    sa    6    Oct 26 2012    NULL    110
Northwind          6.06 MB    sa    9    Oct 27 2012    NULL    110
pubs          5.08 MB    sa    8    Oct 27 2012    NULL    110 

If u have least privileges

 

Syntax:

Exec sp_helpdb pubs 

name    db_size    owner    dbid    created    status    compatibility_level
pubs          5.08 MB    sa    8    Oct 27 2012    Status=ONLINE...,  110

Name of the Database: pubs

DB size in MB/GB

database ID <=> dbid

Created Date.

Status online/offline

compability_level:  110 --> means SQL Server 2012.

Tags: Find Database size in Sql-Server, Finding database sizes in Sql-Server, Find Database Size,Find Data base size in Sql-Server, Find Database ID in SQL,Find Database Status in sql,  Find Database Created Date,Find owner of the Database,Find DBID in SQL-Server,

Insert/Update Unicode text in Sql-Server

Insert/Update Unicode text in Sql-Server


create table unicodeText
(
   text nvarchar(max)
);

go

insert into unicodeText values('हम आपके हैं कौन')
insert into unicodeText values(N'हम आपके हैं कौन')
insert into unicodeText values(N'హం ఆప్‌కే హైన్ కౌన్')
insert into unicodetext values(N' வேண்டும் என மாணவியின் தந்தை')
insert into unicodetext values(N'ಮೋದಿ ಪರ ಸಿನ್ಹಾ ಬ್ಯಾಟಿಂಗ್')

go

Same thing for Update operation
Here is an example

Update unicodetext set  text=N'ಮೋದಿ ಪರ' where text=N'ಮೋದಿ ಪರ ಸಿನ್ಹಾ ಬ್ಯಾಟಿಂಗ್'

RESULT

?? ???? ??? ???
हम आपके हैं कौन
हम आपके हैं कौन
హం ఆప్‌కే హైన్ కౌన్
 வேண்டும் என மாணவியின் தந்தை
ಮೋದಿ ಪರ ಸಿನ್ಹಾ ಬ್ಯಾಟಿಂಗ್


Tags: Unicode text in SQL-Server, Insert unicode Text,Update Uincode text, codepage,Unicodes in SQL-Server

Len in t-SQL/Length of the Column

Len in  t-SQL/Find Length of the Column

Len is a T-SQL String function, displays number of characters in the Column/String variable.

ex:  Find Length of the column

select Len(Description)  from Textbook


Eample 2:

Find Variables Length

 Declare an variable @desc of nvarchar(max)  set some data, and find the length of the string.

declare @desc nvarchar(max);

set @desc=

'New York (Listeni/nuː ˈjɔrk/; locally IPA: [nɪu ˈjɔək]) is a state in the Northeastern region of the United States. New York is the 27th-most extensive, the 3rd-most populous, and the 7th-most densely populated of the 50 United States. New York is bordered by New Jersey and Pennsylvania to the south, and by Connecticut, Massachusetts and Vermont to the east. The state has a maritime border with Rhode Island east of Long Island, as well as an international border with the Canadian provinces of Ontario to the west and north, and Quebec to the north. The state of New York is often referred to as New York State to distinguish it from New York City.';

select len(@desc)


Example 3:

FIND length of multiple columns

select Len(concat(firstname,middlename,lastname)) from Customers
select len(concat(title,firstname,lastname)) from Employees  (northwind DB)

Example 4:

Find Length of the Unicode Column

create table unicodeText
(
   text nvarchar(max)
);

go

insert into unicodeText values(N'हम आपके हैं कौन')

SELECT  datalength([text])
  FROM [tempo1].[dbo].[unicodeText]

Tags:FIND length of multiple columns, len in t-sql,
Find Variables Length,Find Length of the column, column length in t-sql,Find length of the unicode column

Sql Server Escape Character in INSERT/UPDATE

Sql Server Escape Character in INSERT/UPDATE

If u choose string limiter as single quote('), part of the string has single quotation user may get 

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.

 so  insert one more single quote . to solve this.

Insert into Store(StoreName,City,Desc) values('jhon''s store',

                                                 'New York',

                                                'jhon''s store has all apparels'

Same thing for UPDATE statement

tags:Sql Server Escape Characters in Insert/Update,Escape Characters in SQL Server.

Exporting bulk data with sql server's bcp utility

Exporting bulk data with sql server's bcp utility

Step 1)  Open Command Prompt  

type C:\>   bcp 

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]

We need bold faced fields to export bulk data into TextFile.

Export Entire table into textfile

bcp ranking out c:\rankings.txt -T -d tempo1 -S .\sqlexpress2012

TableName: ranking

Direction: out

datafile : c:\rankings.txt

T: trusted connection/Windows Authentication

-d:  Database Name

-S: Sql Server Instance

Exporting Entire Table into Text file C:\rankings.txt

Export by Query into textfile

bcp "select * from ranking " queryout c:\rankings2.txt -T -d tempo1 -S .\sqlexpress2012

Export Stored Procedure Result into Text File 

bcp "exec spranking " queryout c:\rankings2.txt -T -d tempo1 -S .\sqlexpress2012

Tags:
Export by Query into textfile,Export Entire table into textfile,

Exporting bulk data with sql server's bcp utility,bcp syntax,export data from datatable to textfile

Importing Images to SQL Server using T-SQL

Importing Images to SQL Server using T-SQL

Using OpenRowSet you can import Any file into database(pdf,excel,word,xps, etc.,) as a BLOB

Here is an example of importing/inserting images into database.

Step 1) Create City Table

CREATE TABLE [dbo].[City](
    [ID] [smallint] IDENTITY(1,1) NOT NULL,
    [CityName] [nvarchar](50) NULL,
    [Description] [nvarchar](max) NULL,
    [CityImage] [image] NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

It has city name,desc and city Image

Step 2) Insert records with images into   City Table

  insert into City(CityName,[Description],CityImage)
  values('New York',
  'New York  is a state in the Northeastern region of the United States.',
  (select c.* from OpenRowSet(BULK N'C:\Çity\New York.jpg',SINGLE_BLOB) as c))

assuming  New york image exists in C:\city

Step 3) Update image for already existing record

update City set CityImage=(select c.* from OpenRowSet(BULK N'C:\Çity\New York.jpg',SINGLE_BLOB) as c)
where ID=4

Tags:Importing Images to SQL Server using T-SQL,Import image into sql server using tsql,Insert images into sql server using DML, update image data using DML,DML insert,DML update

Import text file into SQL Server using OpenRowSet

Import text file into SQL Server  using OpenRowSet

This example imports text file into SQL-Server 2012 using OpenRowSet command.

Step 1) Create a Datatable (corresponding to columns in text file)

CREATE TABLE [dbo].[players](
    [RK] [varchar](50) NULL,
    [PLAYER] [varchar](50) NULL,
    [Salary (US$)] [varchar](50) NULL
) ON [PRIMARY]

  

Step 2) Create a format file Specific to Text file

  It is using sql server edition 11.0(i.e Sql server 2012)

it has 3 columns

each column can accept 0 to 50 chars

each column is tab separated \t


new row is separated by new line char \r\n

Players.fmt

11.0
3
1       SQLCHAR             0       50      "\t"   1     RK                           Latin1_General_CI_AI
2       SQLCHAR             0       50      "\t"   2     PLAYER                       Latin1_General_CI_AI
3       SQLCHAR             0       50      "\r\n"   3     Salary                Latin1_General_CI_AI
 

Step 3) Test OpenRowSet command

select document.* from openrowset(BULK N'C:\players.txt',formatfile=N'c:\player.fmt' ,firstrow=2) as document

Step 4) Insert into Players Datatable

insert into [dbo].[players]
select document.* from openrowset(BULK N'C:\players.txt',formatfile=N'c:\players.fmt',firstrow=2) as document

Players.txt

RK    PLAYER    Salary
1    Alex Rodriguez    30,000,000
2    CC Sabathia    24,285,714
3    Mark Teixeira    23,125,000
4    Ichiro Suzuki    18,000,000
5    Derek Jeter    15,729,365
6    Mariano Rivera    14,940,025
7    Robinson Cano    14,000,000

Tags: Import text file into sql server using OpenRowSet, Import text file using format file, Import text file using datafile,Import text file using format and datafile,Bulk Import text file using OpenRowSet, Open Row Set.

Import Text file into SQL Server

Import Text file into SQL Server 

Step 1)   Run SQL Server Import and Export Wizard(32-bit/64-bit)

Click on All Programs->Microsoft Sql Server 2012-> Import and Export Data(64-bit)

 Step 2) Specify Data Source as Flat File Source and Text file Location

Step 4)  Results of Flat File Shown below

             

Step 3) Specify Export location Details

in this Destination : Sql Server Native Client 11.0

                                 Server name: .\SqlExpress2012

                                  Database: tempo1 (choose your database)

Step 4) Mapping Source and Destination tables(Leave this option as it is)

Step 5)  RUN THE PACKAGE

Step 6) RUNNING PACKAGE RESULTS

Note:  This will Create new Data Table called Rankings with Columns , Data will be imported into that newly Created Table.

Check in the SQL-Server Instance and in database(tempo1)

Tags:Import text file into SQL Server using Import and Export Wizard, Importing textfile into SQL Server, Exporting Text file and Importing into SQL Server using Wizard.

Sample Data:  Rankings.txt

Concat in Sql Server 2012

Concat in Sql Server 2012

T-SQL String function concat allows us to append/prepend text to a column. or format a column.

Example:  Players Table has  Salary Column, That needs to be updated with currency symbol.

declare @sal varchar(50)
update  [tempo1].[dbo].[players] set @sal=[Salary (US$)], [Salary (US$)]=CONCAT(@sal,'$')

OUTPUT

RK    PLAYER    Salary (US$)
1    Alex Rodriguez    30,000,000$
2    CC Sabathia    24,285,714$
3    Mark Teixeira    23,125,000$
4    Ichiro Suzuki    18,000,000$
5    Derek Jeter    15,729,365$
6    Mariano Rivera    14,940,025$
7    Robinson Cano    14,000,000$