Get all column of a table

Get all column of a table 

SELECT *FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Customers'
go

sp_Help 'Customers'
go

SELECT *FROM sys.all_columns where object_id=OBJECT_ID('Customers')

Search by Column Value in All Referencing Tables in SQL SERVER

Search by Column Value in All Referencing Tables in SQL SERVER

DECLARE @sql nvarchar(255)
DECLARE @tablename nvarchar(255)
DECLARE @var varchar(50)
DECLARE @columnname nvarchar(100)
--Supply the column name and value here
SET @columnname= 'FirstName'
SET @var='FirstName'
DECLARE TableCol CURSOR FOR
SELECT t.name           
      FROM MyPractice2014.sys.columns AS c 
            INNER JOIN 
             MyPractice2014.sys.tables AS t 
            ON t.[object_id] = c.[object_id] 
        WHERE     UPPER(c.name) = @columnname order by t.name
OPEN TableCol
FETCH TableCol INTO @tablename
-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN
-- In this query 'Table_Name' is dummy column that'll display the current tablename as column header
SELECT @sql='select ''TABLE_NAME'' as '''+@tablename+''', * from '+@tablename+' where '+@columnname+'='+@var
EXEC(@sql)
--print @tablename
FETCH TableCol INTO @tablename
END
CLOSE TableCol
DEALLOCATE TableCol
RETURN
GO

list all table without clustered index

list all table without clustered index

SELECT DISTINCT [TABLE]=OBJECT_NAME(OBJECT_ID) FROM sys.indexes WHERE index_id=0
AND OBJECTPROPERTY(object_id,'IsUserTable')=1 ORDER BY [TABLE]

Get The list of Tables Without Primary Keys

Get The list of Tables Without Primary Keys

SELECT Name FROM sysobjects 
WHERE ID NOT IN (SELECT b.Id FROM Sysconstraints b, sysobjects c WHERE c.type ='k' AND c.Id=b.constid) AND 
type ='U' ORDER BY name

Get table definitions through T-SQL

--query table definitions

use AdventureWorks

DECLARE @TableName VARCHAR(128)
DECLARE @TableSchema VARCHAR(128)
--Identify the table name
SET @TableName ='Department'
--identify the table owner ex. dbo
SET @TableSchema='Humanresource'

SELECT Column_Name, COLUMN_DEFAULT,IS_NULLABLE AS ALLOW_NULL,DATA_TYPE,ISNULL(CHARACTER_MAXIMUM_LENGTH,0)[Max Length],
ISNULL(NUMERIC_PRECISION,0)[Precision],
ISNULL(NUMERIC_SCALE,0)[Scale]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@TableName
AND TABLE_SCHEMA=@TableSchema ORDER BY ORDINAL_POSITION