Well, ever wanted to know what was happening on disk....You could with FN_vitrualfilestats....Here is a NICE SPROC that samples all databases and puts it in a table. You can choose if you want to Retain values in the table or that it must be cleaned every time the sproc runs.( Put this data in a […]
2003-01-31
951 reads
The sp_CheckAndDelete stored procedure specially created for handling process of data deleting efficiently in MS SQL Server 2000 tables, as it is too difficult to handle this process when dependants exist for record to be deleted. When the deleting process is done with sp_CheckAndDelete stored procedure, you will be informed the status of the process […]
2003-01-30
216 reads
This little query gives the table name and rowcount for each table in the database that its run.
2003-01-30
303 reads
Lets you search all db objects for a string.EXECUTE spFindTextInObjects 'UNION ALL', 1 GOEXECUTE spFindTextInObjects 'XML EXPLICIT'GOEXECUTE spFindTextInObjects 'DECLARE%CURSOR', 1GOEXECUTE spFindTextInObjects '@@IDENTITY', 1GO
2003-01-29
300 reads
I had recent request for a script to list all the views for a database along with the associated tables the columns depend on. It is a rather simple script but as I have not seen a similar one, I thought I would share it with my fellow dbas. It returns information about views specific […]
2003-01-23
507 reads
It is easy enough to detach and reattach databases in SQL 2000 providing you have less than 16 files to attach.I manage some very large databases and these sometimes need to be detached and reattached (i.e. for moving a database file from one volume to another).I have more than 16 files to contend with on […]
2003-01-22
2,918 reads
Something I really hate is typing something repetitive. To prepare for our Disaster Recovery plan, I wrote this script.It is a bit strange in that it requires 2 run iterations to generate the desired result.I use a naming standard for backup files of the form:D_DBName.Bakand a naming standard for database files of the form:DBName_Dx.Mdf for […]
2003-01-22
642 reads
You may be a tidy person who likes to keep naming standards for everything in SQL Server. It is a good practice since it makes generating scripts for everyday DBA tasks a lot simpler. I make all database files follow the following standard:DBName_D1.mdf for primary filegroup dataDBName_I1.ndf for index filegroupDBName_L1.ldf for transaction […]
2003-01-22
335 reads
This isn't a "script" per se - but rather an observation about a common issue in writing scripts.I want to alert SQL programmers to the issues surrounding the use of single-line comments. I try to avoid these whenever possible, and use block comments instead (except, of course, when commenting out the keyword GO).The reason is […]
2003-01-21
313 reads
Unlike shrinking transaction log, shrinking a data file seems to be tough. A lot of people complained about the "shrink database" option in EM not working, or DBCC SHRINKDATABASE not working, or DBCC SHRINKFILE not working. I have found that a data file will eventually shrink given enough try, time and patience. I've put everything […]
2003-01-14
437 reads
By Steve Jones
Yesterday was the first day of sessions at the PASS Data Community Summit (after...
This month’s blogging party is hosted by Jeff Ianucci – his challenge is to...
As for me, I'll keep writing whenever time permits. I don't expect every post...
Comments posted to this topic are about the item Pay for Performance, which is...
Comments posted to this topic are about the item Switching Schemas I
Comments posted to this topic are about the item Enforcing Unique Customer Emails When...
I run this query:
SELECT s.name AS schemaname , o.name AS objectname FROM sys.objects o INNER JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.name = 'Beer'I get one row back for an object named dbo.beer. There is an ETL schema and both of these are nonpartitioned tables. I run this code:
ALTER TABLE dbo.beer switch TO etl.beerWhat happens? See possible answers