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 - 2,887 views

List 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 - 57,993 views

SQL 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 - 3,160 views

Select 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 - 2,301 views

Select 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 - 2,647 views

Unable 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,662 views

Setting 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,201 views

XMLHTTPRequest 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,317 views

T-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,606 views

Select 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,421 views

Changing 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,374 views

Enabling 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,844 views

Full 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,277 views

Enabling 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 - 64,884 views

Return 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,722 views

Handling 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,137 views

Change 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,002 views

Passing 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 - 11,350 views

SQL 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,316 views

GRANT 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 - 59,626 views