Mahipal Padigela's Blog

Table Row counts Sql Server 2005

You can get the same in SSMS, by selecting ‘Reports’ option in the Database context sensitive menu

SELECT
	distinct Table_Name = object_name(object_id),
	T.TABLE_SCHEMA,
	Total_Rows =  st.row_count
FROM sys.dm_db_partition_stats st INNER JOIN information_schema.tables T
on object_name(object_id) = T.TABLE_NAME
where object_name(object_id) not like 'sys%' and object_name(object_id) not like 'queue%'
ORDER BY object_name(object_id)

sqlcmd & dtexec params and syntax

SQLCMD
sqlcmd -S MySERVER -E -d master -i APPLY_DB_SECURITY.sql -o LOG\APPLY_DB_SECURITY.log

DTEXEC
dtexec /FILE “\\MYSERVER\BI Environments\myfolder\ETL\SSIS\MasterETL\LoadControl.dtsx”
/CONNECTION SSISControl;”\”Data Source=MYSERVER;Initial Catalog=mydb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\”" /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF
/SET “\Package.Variables[User::RootPath].Properties[Value]“;”\\MYSERVER\BI Environments\mpadigela_dev1\ETL”
/SET “\Package.Variables[User::ServerName].Properties[Value]“;”MYSERVERNAME”
/SET “\Package.Variables[User::EnviromentName].Properties[Value]“;”myEnvironment_”
/REPORTING EWCDI

Tags: DTEXEC, SQLCMD

Speedup SSMS and Visaul Studio

SSMS

“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe”  /nosplash /S MYSERVER /E /D MYDB

so, no splash screen, connects to MYSERVER server,  MYDB database using Integrated Security.

VS

“C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe”  /nosplash

Tags: SSMS, Visaul Studio

T-SQL String Concatenation using FOR XML PATH(”)

Sometimes we need to convert (concatenate) string column values for a given group into a
comma(or any character) separated list of values.

eg:
I have this:

I want this:

here is one way of doing this in T-SQL using FOR XML PATH(”) (only works with SQL Server 2005 and above)

select NoteID, STUFF(
	(SELECT  ',' + a.Comments  AS [text()]
	from Notes  a
	where a.NoteID = b.NoteID
	Order by a.Comments
	for xml PATH('')),1,1,''	) AS Comments_Concatenated
 from Notes b
group by NoteID
ORDER BY NoteID

or convert all rows into csv..

SELECT 'MP' NAME
into #tt1
UNION
SELECT 'BP' NAME
UNION
SELECT 'HP' NAME

select name from #tt1

declare @strTemp as varchar(100)

Select @strTemp = STUFF(
	(SELECT  ',' + a.Name  AS [text()]
	from #tt1  a
	for xml PATH('')),1,1,'' )  

print @strTemp

STUFF function removes the leading comma

Tags: FOR XML PATH(''), String Concatenation, STUFF

Date to/from ID Conversion – TSQL

/*Convert DateTime to ID (yyyymmdd)*/
select replace(convert(char(10),getdate(),120),’-',”)

/*Convert DateID (yyyymmdd) to Date*/
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), 20080922))

Tags: Convert, DateID, DateTime, T-SQL

Troubleshooting Performance Problems in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Memory configuration settings in SQL Server

some useful links on SQL Server memory management:

http://sqlnerd.blogspot.com/2007/07/further-explanation-of-sql-memory-use.html

How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

http://support.microsoft.com/kb/918483/en-us

Querying table, column names and datatypes – Sql Server 2005

Select
a.name TableName,b.name ColumnName,c.name DataType
FROM sys.tables a
	inner join sys.columns b on a.object_id=b.Object_id
	inner join sys.types c on b.system_type_id = c.system_type_id
order by a.name,b.name

Tags: column names, datatypes, Sql Server 2005

Which version, level, edition of Sql Server am I using?

SELECT
	SERVERPROPERTY('productversion'),
	SERVERPROPERTY ('productlevel'),
	SERVERPROPERTY ('edition')

Tags: SERVERPROPERTY, Sql Server, version