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

Advertisements

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)

There some setting changes between SQL Server 2005 and 2008. One of them is by default you can edit only 200 records. You can modify settings to allow you to SELECT or Edit all records following the below steps. 

Modify  “Edit Top 200 Rows”:

Tools->Options->SQL Server Object Explorer>Commands

 If you would like to edit more than 200 rows, you can choose the “Value for Edit Top <n> Rows command” setting from menu.

 If you would like to select more than 1000 rows, you can choose the “Value for Select Top <n> Rows command” setting from menu.

If you want to SELECT or EDIT for all rows as before, you can simply change the number <n> to 0.


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’


How to Show File Extension in Window 2008 Server and Windows 7

If you open a folder from a Window 2008 Server, you cannot see your files extension any more.
There is a setting you can change to see the extension again.
Follow these steps:

In Window Explore
>>Click on Organize(Up-Left corner)
>> Folderand search options
>>Under View tab
>>Uncheck the checkbox for: Hide extensions for known file types

Go back to your folder and you will see your files with extensions!


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, namefilename 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