Epic Puck

This query will get you all of the tables, columns and column details for a database. You will need to substitute the column name with the name of the column that you are looking for.

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
   AND syscolumns.name like '%column name%'
ORDER BY sysobjects.name,syscolumns.colid

I work with six other developers. We don't have a SQL DBA per se. This means that sometimes my colleagues or even myself will have open SPIDs preventing a SQL RESTORE.

So, who locked the database?

select spid, status, loginame, hostname, blocked, db_name(dbid), cmd, *
 from master..sysprocesses where db_name(dbid) = 'database name'

I have adopted this tool at work for its code generation ability.  It generated C# and SQL Stored Procedures that I needed for my latest update to our company software.  It just works.  Nothing fancy.  No bells.  No marketing.  It just works.

You can download yours here.

Thanks!

I have run into this.  I am working against a database with millions of rows and possibly hundreds of tables.  How can I find out if I have used that column before and quickly?

to find a column name from all columns in a 2008 (R2) database:

SELECT distinct name
FROM sys.all_columns
where name like '%column name%'

to display all of the tables as well as their row counts from a 2008 (r2) database:

select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows
from sysindexes a inner join sysobjects b on a.id = b.id
inner join INFORMATION_SCHEMA.TABLES c on c.TABLE_NAME=convert(varchar(30),object_name(a.id))
where c.TABLE_CATALOG='database name'

I hope that these help you like they helped me.

DECLARE @oldOwner sysname, @newOwner sysnameSELECT @oldOwner = 'old_owner'

, @newOwner = 'dbo'

select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.TABLE_SCHEMA)+'.'+QUOTENAME(a.TABLE_NAME)+''','''+@newOwner+''''

from

INFORMATION_SCHEMA.TABLES a

where

a.TABLE_SCHEMA = @oldOwner

AND

OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.TABLE_SCHEMA)+'.'+QUOTENAME(a.TABLE_NAME)), 'IsMSShipped') = 0