Transact-SQL

This category contains useful information relating to Transact SQL (TSQL).

List of primary key fields within a database

0
3 years ago
in
The query below lists the Table Name, Column Name, and Column ID for all Primary Key columns within a database SELECT  t.name AS TableName, c.name AS ColumnName, c.colid AS ColumnID FROM      sysindexes i   INNER JOIN sysobjects t       ON i.id = t.id   INNER JOIN sysindexkeys k     ON i.indid = [...]

List of foreign key relationships

0
3 years ago
in
Use the query below to produce a list of foreign key relationships: select f.name AS ReferencingTable, fc.name AS ReferencingColumn,        r.name AS ReferencedTable, rc.name AS ReferencedColumn from sysreferences s inner join sysobjects f on s.fkeyid = f.id inner join sysobjects r on s.rkeyid = [...]

List of table columns, with data types

2
3 years ago
in
The Transact-SQL query below, returns the table name, column name, data type, and maximum length. This is useful when producing database documentation SELECT       schema_name(O.schema_id) + '.' + O.name AS TableName,       C.name AS ColumnName,       T.name AS Type,       C.max_length AS [...]

Apply permissions to all stored procedures or tables

0
3 years ago
in
DECLARE @SQL varchar(100) DECLARE cObjects CURSOR FAST_FORWARD FOR     SELECT 'GRANT EXECUTE ON ' + name + ' TO [usrTest]' AS SQL     FROM   sysobjects     WHERE  xtype in ('P') /* Or, to Grant select, insert, update, and/or delete permissions, use the following select statement instead of the [...]

Problem with ISNULL

0
3 years ago
in
While going through “Question of the day” from http://www.sqlservercentral.com/, I discovered an issue with the ISNULL function. If you were to run the query below, you will be suprised to hear, that the result returned is “A”.  I am sure you would expect the result to be [...]

COALESCE – Return first non-NULL value from multiple choices

0
3 years ago
in
Whilst studying for my MCITP Database Developer certification, I have come across a very useful function that returns the value of the first non-NULL value from multiple choices: SELECT LastName, FirstName, COALESCE(MobilePhone, HomePhone) AS Phone If the MobilePhone field is blank, it will use the [...]

Create a stored procedure using a parameter default of NULL

0
3 years ago
in
The default value of a parameter can be specified as NULL. In this case, if you do not supply a parameter, SQL Server executes the stored procedure according to its other statements. No error message is displayed. The procedure definition can also specify that some other action be taken if you do [...]
Go to Top