RSS

Category Archives: SQL

Search stored procedure for table

Credit to: http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/

SELECT DISTINCT o.name, o.xtype
 FROM syscomments c
 INNER JOIN sysobjects o ON c.id=o.id
 WHERE c.TEXT LIKE '%tablename%'
Advertisements
 
Leave a comment

Posted by on April 19, 2013 in SQL

 

MS SQL rename column

exec sp_rename ‘tableName.ColumnName’, ‘newColumnName’, ‘COLUMN’

EXEC sp_rename 'LeadTracking.cSource', 'cSourceLookupId', 'COLUMN'

Original source

http://dotnetcoderoom.wordpress.com/2008/09/02/rename-a-table-column-in-ms-sql/

 
Leave a comment

Posted by on May 19, 2011 in SQL

 

Tags:

Get all columns of a certain type from database

How do you get all the tables and columns in your database where the type is like money?

select
     o.name as tableName,
    c.name as ColumnName,
    t.Name as ColumnType,
    o.type as ObjectType,
    u.name as SchemaName
from syscolumns c
inner join sysobjects o
    on o.id=c.id
inner join sysusers u
    on u.uid= o.uid
inner join sys.types t
    on t.system_type_id = c.xtype
where t.Name like '%money%'

 
Leave a comment

Posted by on April 12, 2011 in SQL

 

Tags:

Get your Foreign Keys

How do you query your ms sql database and find out what columns in your table are foreign keys?

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER],

C.TABLE_SCHEMA [PKTABLE_OWNER],

C.TABLE_NAME [PKTABLE_NAME],

KCU.COLUMN_NAME [PKCOLUMN_NAME],

C2.TABLE_CATALOG [FKTABLE_QUALIFIER],

C2.TABLE_SCHEMA [FKTABLE_OWNER],

C2.TABLE_NAME [FKTABLE_NAME],

KCU2.COLUMN_NAME [FKCOLUMN_NAME],

RC.UPDATE_RULE,

RC.DELETE_RULE,

C.CONSTRAINT_NAME [FK_NAME],

C2.CONSTRAINT_NAME [PK_NAME],

CAST(7 AS SMALLINT) [DEFERRABILITY]

FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA

AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA

AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2

ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA

AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2

ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA

AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME

AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION

WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'

and

C.TABLE_NAME ='TimeSpan'
 
Leave a comment

Posted by on April 5, 2011 in SQL