This script will show you how much diskspace can be reclaimed by shrinking all the logfiles of userdatabases using a DBCC SHRINKFILE(2) command. Often the spacegain is much smaller then expected. SQL Shrinks logfiles the end of the file until it finds the active part of the logfile. (If the active part of the logfile […]
2005-03-21 (first published: 2005-03-04)
449 reads
This stored procedure is created to be invoked (in my case by SQL Server scheduled job), and will then create a tracefile to a specified file location (ensure that if this is NOT local, your SQL Server Agent runs on a domain account that has access to that drive. The Stored procedure will create files […]
2005-03-16 (first published: 2005-03-04)
456 reads
This Procedure will display info regard to when the last time database was used. How to use it--------------Exec master.dbo.Sp_LastTimeDBUsed 'pubs'
2005-03-15 (first published: 2005-03-02)
1,093 reads
Easily extractss the complete stored procedure call hierarchy from "sysdepends" table of SQL Server and represent it in a pseudo-graphical tree-view.The script uses a temporary table (automatically created/dropped) to hold children and parents of relationships. For representing the call hierarchy the table contains a level field (depth) and a field containing the "enumerated path" (using […]
2005-03-14 (first published: 2005-03-01)
4,049 reads
/*--Objective : To Write a given string on to a given file--Created by: Helen--Date : Jan 4, 2005--Execution : SELECT * FROM DBO.Ufn_WriteToFile('D:\Testing\dbcc.TXT','Hello world')*/
2005-03-10 (first published: 2005-02-21)
606 reads
2005-03-09 (first published: 2005-02-21)
236 reads
Here's an alternate way of removing multiple white spaces by using the REPLACE command.
2005-03-08 (first published: 2005-02-22)
191 reads
This script will BCP all user tables of a database to individual files. The file names are tablenameyyyymmddhhmmss.dat To executeusp_bcp_out_alltables 'database','path','server'You will need to change your default 1) path2) server
2005-03-07 (first published: 2005-02-23)
300 reads
This script creates a table based function that returns time dimension that would typically be seen in a data warehouse. The time dimension has aggregate values for week, month, quarter, half, and year.
2005-03-04 (first published: 2005-02-23)
1,271 reads
This script will give the text of all the procedures with occurences of a specifc variable. Syscomments has a limitation of 4000 characters but this script overcomes that as it uses DMO. The output is generated in a text file. If you want the name of the procedure only. replace the line where it says […]
2005-03-03 (first published: 2005-02-25)
193 reads
By Steve Jones
Thanks to everyone that attended my sessions at VS Live San Diego yesterday. It...
By Steve Jones
I was creating a question on sp_readerrorlog and realized that this procedure is different...
By SQLPals
Why sys.fn_dblog Is Undocumented And Why It's Still There Why sys.fn_dblog...
Comments posted to this topic are about the item Implementing Type 4 Slowly Changing...
Comments posted to this topic are about the item The Disabled Index
Comments posted to this topic are about the item Server-Level Table sizes
I run this code on SQL Server 2022.
CREATE TABLE Drink
(
drinkid INT NOT NULL
CONSTRAINT DrinkPK PRIMARY KEY CLUSTERED,
drinkname VARCHAR(20),
rating NUMERIC(2, 1)
)
GO
INSERT INTO Drink
(
drinkid,
drinkname,
rating
)
VALUES
(1, 'Margarita', 4.5),
(2, 'Mojito', 4.3),
(3, 'Old Fashioned', 4.7),
(4, 'Martini', 4.4),
(5, 'Cosmopolitan', 4.2)
GO
ALTER INDEX drinkpk ON dbo.drink DISABLE
GO
SELECT * FROM dbo.Drink
GO
What is the result? See possible answers