GeekZilla
T-Sql Articles
Get the progress of active SQL backups using TSQL
Get the progress of active SQL backups using TSQL The following script returns the status of any running backups on Microsoft SQL Server SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete
Paul Hayman - 4,091 viewsList all Tables SQL Server
List all Tables SQL Server Here's a tiny bit of SQL for listing all the Tables in a database: SELECT * FROM sys.tables
Paul Hayman - 66,242 viewsSQL Server Database, kill active processes and take it offline
SQL Server Database, kill active processes and take it offline Use the following TSQL to kill the active processes on a database and take it offline. DECLARE @DatabaseName nvarchar(50) SET @DatabaseName = N'databasename' DECL
Paul Hayman - 4,392 viewsSelect list of all tables in a MS SQL Server database including row count
Select list of all tables in a MS SQL Server database including row count The following script lists all tables in a database and their row count. SELECT [TableName] = so.name, [RowCount]&n
Paul Hayman - 3,476 viewsSelect a list of databases missing Primary keys
Select a list of databases missing Primary keys The following T-SQL returns a list of tables in a database which have no primary key set. select * from sys.tables where object_id not in ( select o
Paul Hayman - 3,773 viewsUnable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey'
Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' I was getting the following error when I attempted to create a new job in SQL Server Agent: Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServe
Ric Hayman MCPD - 12,733 viewsSetting default collation on a column
Setting default collation on a column The following will set the collation on a column in a table back to Database Default without dropping and recreating the table (unlike Enterprise Manager) ALTER TABLE [MyTable] ALTER COLUMN [MyColumn]
Paul Hayman - 9,266 viewsXMLHTTPRequest in Stored Procedure
XMLHTTPRequest in Stored Procedure I recently had a requirement to call a webservice from a stored procedure. I looked into doing this by writing a CLR assembly in SQL 2005. There were a few articles online describing this but working in a large organisation I knew that getting the thi
Dave Howard - 20,863 viewsT-SQL script automatically adds auditing to a table
T-SQL script automatically adds auditing to a table The script below looks at the structure of the table you specify and creates a new table called xxx_Audit with the same structure plus a timestamp, less any constraints/unique keys etc. It then creates a new trigger on the original tabl
Dave Howard - 30,732 viewsSelect Column Information using SQL Server
Select Column Information using SQL Server Use this T-SQL to select infomation about a specific table/columns, Useful for automated scripts. DECLARE @tablename varchar(100) SET @tablename = N'myTable' SELECT clmns.name&n
Dave Howard - 154,876 viewsChanging Schema for a group of objects
Changing Schema for a group of objects This script moves all of you stored procedures from one Schema to another. This was really handy when using a Typed Dataset with SQL 2005 and stored procedures. The wizard creates stored procedures in the users Schema (and complains if it does not e
Dave Howard - 11,446 viewsEnabling Agent XPs on a new SQL Server 2005 install
Enabling Agent XPs on a new SQL Server 2005 install I had to do this prior to creating a maintainence plan for a database on my development box. When i first tried to create the plan I was given a message that Agent XPs are not enabled and that I should run sp_reconfigure. Run t
Dave Howard - 28,909 viewsFull Text Search in SQL 2005
Full Text Search in SQL 2005 Full Text Search was installed when SQL Server was installed and a service was running, so I expected it to just be sitting there waiting. This is unlike SQL 2000, where I lost count of how many times I had to install it on different servers. Was I wron
Ric Hayman MCPD - 60,379 viewsEnabling Service Broker
Enabling Service Broker The following T-Sql enables or disabled service broker on SqlServer 2005. The Service Broker is required by .net for SqlCacheDependency support -- Enable Service Broker: ALTER DATABASE [Database Name] SET ENABLE_BROKER; -- Di
Paul Hayman - 65,088 viewsReturn new identity from Strongly Typed Dataset DataTable.Insert method
Return new identity from Strongly Typed Dataset DataTable.Insert method Datasets are pretty good at auto generating stored procedures and wrapping c# code around them for you. However with the insert method you often want to do something with the object that you have just inserte
Dave Howard - 26,823 viewsHandling hierachical structures in SQL
Handling hierachical structures in SQL Dealing with tree structures in SQL can be tricky. A common solution is to have a self referencing table with a primary key and parent key column. This allows for any depth of tree and allows each 'node' to have zero or more child nodes. Be
Dave Howard - 5,192 viewsChange the Owner for Stored Procedures
Change the Owner for Stored Procedures This one comes from Tim Page, if you ever need to change the owner of several Store Procedures at the same time then this is the T-SQL for you. And this even supports SQL Sever 2005.
Greg Duffield - 33,775 viewsPassing CSV to Stored Procedure
Passing CSV to Stored Procedure There are many occasions when it would be easier to pass an array of values to a Stored Procedure instead of looping. This can be done and is very simple to use once implemented, all it needs is a new UDF (User Defined Function).
Greg Duffield - 12,042 viewsSQL Server TABLE Variable
SQL Server TABLE Variable When you need a temporary table in SQL Server 2000 (upwards) many people will implement a standard temporary table i.e. SELECT ProductID, ProductName, &nbs
Greg Duffield - 2,384 viewsGRANT Permissions on all of your Stored Procedures using a query
GRANT Permissions on all of your Stored Procedures using a query Permissions are an essential security feature, and you'll often create Roles and Users when you copy your Database live. Rather than using the permissions dialog boxes in Enterprise Manager you can write some T-SQL to perform
Paul Marshall - 60,748 views