Check Tables without Any Record in a Database

–Method 1:

DECLARE @s NVARCHAR(4000)

CREATE TABLE #NoRecords

(

tablename NVARCHAR(4000),

rCnt INT

)

SELECT @s = ‘SELECT TableName, rCnt from

(select o.name as TableName, max(i.rowcnt)

as rCnt from sys.sysobjects o inner join sys.sysindexes i

on o.id = i.id where o.type = ”U”

group by o.name) t WHERE rCnt=0′

INSERT #NoRecords(TableName, rCnt)

EXEC sp_executesql @s

SELECT TableName FROM #NoRecords

DROP TABLE #NoRecords

–Method 2:

use mytest1

go

Create table #t (name varchar(200), rows int,

reserved varchar(200),data varchar(200)

,index_size varchar(200)

,unused varchar(200))

insert into #t

EXEC sp_MSforeachtable @command1=’ EXEC sp_spaceused ”?” ‘

select * from #t where rows=0

drop table #t

–Method 3:

SELECT name, rCnt FROM (SELECT b.name, SUM(A.reserved_page_count * 8) rCnt

FROM sys.dm_db_partition_stats a INNER JOIN sys.OBJECTS b

ON A.object_id = b.object_id AND b.name NOT Like ‘sys%’

GROUP BY name) t

WHERE rCnt=0

Maximum size for SQL Server Objects’ Identifier Length — 128

There is a maximum size for SQL Server object identifier: 128 Identifier length (in characters).

Table name, column name….

Modify the default setting: “Edit Top 200 Rows” or “Select Top 1000 Rows” in SQL Server 2008 Management Studio (2)

How to Change All Database Recovery Mode

Here is a code snippet to change database recovery mode to SIMPLE or FULL for all user databases in  the server instance.

exec sp_MSforeachdb ‘IF DB_ID(”?”) > 4

Begin

EXEC(” ALTER DATABASE [?] SET RECOVERY SIMPLE ”)

End’

exec sp_MSforeachdb ‘IF DB_ID(”?”) > 4

Begin

EXEC(” ALTER DATABASE [?] SET RECOVERY FULL ”)

End’

Change Database Logical Name — the Easy Way.

When you create a new database from another database’s backup, your database is inhereting the old database’s logical file names for both data and log files.

You can find out the name by run this script;

<code> Use yourDBName

SELECT fileid, name,  filename FROM  sysfiles

</code>

The fieldid 1 is for data file record and the fieldid 2 is for log file record

You can  run this set of script to change these names as in the following example:

USE mytest1

–rename data file

ALTER DATABASE mytest1 MODIFY FILE (NAME = mytest1, NEWNAME = mytest1New)

GO

–rename log file

ALTER DATABASE mytest1 MODIFY FILE (NAME = mytest1_log, NEWNAME = mytest1New_Log)

GO

SELECT fileid,name,filename FROM sysfiles

But there is an easy way to change these names after you create your database from SSMS.

Here is the steps to rename the Logical Names: 

From SSMS: Right Click on Database >> Properties>>(Next Window) Files >>
You can change the Logical Name to reflect the new database name for both mdf and ldf files.

If you do want to modify all user database’s logical name that is not aligned with the database name, you can use a script posted on this thread to generate the command to run in a batch.

https://jingyangli.wordpress.com/2011/12/08/generate-script-to-modify-logical-name-of-databases/

Optional Search Parameters with SQL Server 2008 R2

We can have a compact solution to skip some search conditions in a where clause but this query can become a victim of Parameter Sniffing with degraded performance.

For this reason, there is a way to force the query to recompile each time at the end of the WHERE clause to provide an improved performance boost in SQL Server 2008 R2:

WHERE (col1 =@p1 OR @p1 IS NULL)
AND  (col2 =@p1 OR @p2 IS NULL)
AND  (col1 =@p13 OR @p3 IS NULL)
ORDER  BY  col1
OPTION (RECOMPILE)

If you are intertest in this topic, you can find this link for other solutions too:

http://www.sommarskog.se/dyn-search-2008.html