How To Find The Tables And Columns Referenced By A Foreign Key Using T-SQL



TODO:

Have you ever wanted to find the table and column names referenced in a Foreign Key Constraint using T-SQL?

 

SOLUTION:

SELECT 
   OBJECT_NAME(fk.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS fk
INNER JOIN 
   sys.foreign_key_columns AS fkc 
      ON fk.OBJECT_ID = fkc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fkc.referenced_object_id
WHERE 
   OBJECT_NAME (fk.referenced_object_id) = 'YourTableGoesHere'

 

NOTES:

There are no notes on this topic.

How To Create A Table From A SELECT Statement



TODO:

Have you ever wanted to create a table from the results of a SELECT statement?

 

SOLUTION:

 

select * into newtable_tmp from oldtable

 

 

NOTES:

This statement will select all columns and records from the table "oldtable", and create a new table called "newtable_tmp" that contains the results of the SELECT statement.

How To Find Or List All Column Names and Information For A Table Using T-SQL



TODO:

Have you ever wanted to get a list of all of the columns in a database table, and their details, ordered by name?

 

SOLUTION:

 

SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,ISNULL(CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH),'')
  ,IS_NULLABLE
  ,ISNULL(COLUMN_DEFAULT,'')
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME = 'tbl_name_here' 
ORDER BY 
  COLUMN_NAME ASC;

 

NOTES:

No notes on this topic.

Re-Identity a Table in SQL Server



TODO:  Have you ever wanted to re-indentity a column in SQL Server?

 

SOLUTION

T-SQL

 

DECLARE @Count INT
SET @Count = 0

UPDATE [dbname].[dbo].[table]
SET  @Count = [id] = @Count+1

 

 

NOTES:

Identity Insert must be enabled.

"Id" is the name of the identity column.