DECLARE @s NVARCHAR(4000)
CREATE TABLE #NoRecords
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
Create table #t (name varchar(200), rows int,
reserved varchar(200),data varchar(200)
insert into #t
EXEC sp_MSforeachtable @command1=’ EXEC sp_spaceused ”?” ‘
select * from #t where rows=0
drop table #t
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
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)Posted: November 22, 2011
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.
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
EXEC(” ALTER DATABASE [?] SET RECOVERY SIMPLE ”)
exec sp_MSforeachdb ‘IF DB_ID(”?”) > 4
EXEC(” ALTER DATABASE [?] SET RECOVERY FULL ”)
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!
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
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:
–rename data file
ALTER DATABASE mytest1 MODIFY FILE (NAME = mytest1, NEWNAME = mytest1New)
–rename log file
ALTER DATABASE mytest1 MODIFY FILE (NAME = mytest1_log, NEWNAME = mytest1New_Log)
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.
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:
If you are intertest in this topic, you can find this link for other solutions too: