Record Count of all table

To get the record count of each table in the database at once.

CREATE TABLE #RecordCount
(
    TableName varchar(500),
    NoOfRows int
)

EXEC sp_MSForEachTable @command1='INSERT #RecordCount(TableName ,  NoOfRows) SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName , NoOfRows  FROM #RecordCount ORDER BY TableName , NoOfRows DESC

Date Functions in TSQL

To get the first date of the week by passing the week id

dateadd(wk, datediff(wk, 0, getdate()), 0)

To get the last date of the week by passing the week id

dateadd(wk, datediff(wk, 0, getdate()), 0) + 6

Check whether xp_cmdshell is disabled or not

Xp_Cmdshell

- Spawns a Windows command shell and passes in a string for execution.

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed.

xp_cmdshell can be enabled and disabled by using the Policy-Based Management or by executing sp_configure

To check whether xp_cmdshell is disabled or not. Run the following Query.

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value

FROM  sys.configurations

WHERE  name = 'xp_cmdshell' ;

To enable the xp_cmdshell, you can refer the following msdn doc.

Reference : http://technet.microsoft.com/en-us/library/ms175046(v=sql.105).aspx

                 http://technet.microsoft.com/en-us/library/ms190693(v=sql.105).aspx

Enable SQL-Server Agent for non-admin User

Non-admin users will have public access to their databases, and usually SQL Server agent won't show in their Management studio. If you want the public user to create or manage or execute the Jobs in the SQL SERVER, you have to add 3 important SQL Agent fixed database roles to the msdb database.

-  SQLAgentUserRole
-  SQLAgentReaderRole
-  SQLAgentOperatorRole

How to add those roles

1. Navigate through Object Explorer -> Security -> Logins(select the user, then right click) -> Properties -> User Mapping -> Login Properties.

2. Select msdb database.

3. Select SQLAgentUserRole, SQLAgentReaderRole and  SQLAgentOperatorRole under Database role membership for : msdb.

That's it. You are good to go....

Compare the string, int and nText with a string in TSQL

Compare the string, int and nText data type with a string data type in  CASE WHEN TSQL

In this case, Column1 is a string, 
    
    select case when column1 = 'sometext' then 1 else 0 end
    from table1

In this case, Column1 is an integer, 

    select case when cast(column1 as varchar(max)) = 'sometext' then 1 else 0 end
    from table1

In this case, Column1 is a nText, 

    select case when cast(column1 as nvarchar(max)) = N'sometext' then 1 else 0 end
    from table1

Install PostgreSQL in Mac OX

You can install the PostgreSQL in MacOX in either way using Graphical Interface or through HomeBrew.

Click Here to choose the options : http://www.postgresql.org/download/macosx/

Here, I have used  Graphical installer

Before installing always make sure you are having the maximum shared memory. It is safe to begin the installation, it wont reserve any memory, so there in no harm...

To check the Shared memory : $ sysctl -a

kern.sysv.shmmax=1610612736
kern.sysv.shmall=393216
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.maxprocperuid=512
kern.maxproc=2048

If you don't have this config, please update your file $sudo vi /etc/sysctl.conf
[You can also get this info in Readme file.]

After editing, restart the machine, to install Postgres.

Double click the "postgresql-9.3.0-1-osx" and just follow the instruction.

To check whether Postgres is installed or not,

Run this cmd on the terminal :
psql -p 5432 -h localhost -U postgres --password

If it comes to the promt "postgres=#", then you are all set... Quit from the postgres console "\q"

Create table with string identity column

First, You can not directly create a string IDENTITY column in table definition, But we can achive this following ways

1. We can create a compute column using the IDENTITY column. Compute column means evaluate the value using expression.

Create Table TableName1

(
ID int IDENTITY(1,1),
ComputeStrID As 'AnyStrValue' + CAST( ID as Varchar),
AnyOtherColumn varchar(12)
);


insert into TableName1(AnyOtherColumn) values ('Test')
insert into TableName1(AnyOtherColumn) values ('Test2')
insert into TableName1(AnyOtherColumn) values ('Test3')
insert into TableName1(AnyOtherColumn) values ('Test4')


select * from TableName1

Constrain : Only one identity column per table is allowed.

2. In Code, we can manipulate the db identity value while showing the result in screen, somethink like

String.Format("0{1:000}","AnyStrValue", db.ID)
code is in C#