MSSQL: Add a new column to table

ALTER TABLE <table name> 
ADD <new column name> <data type> NOT NULL
GO


ALTER TABLE <table name> 
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
Add foreign Key

ALTER TABLE <Table_Name> ADD FOREIGN KEY(<ColumnName>) REFERENCES <Primary_Table_Name>(<Primary_column_Name>)

ALTER TABLE dbo.AssetInventory ADD FOREIGN KEY(RegionID) REFERENCES Region(RegionID)

Example:

ALTER TABLE dbo.AssetInventory ADD ASSET_PARENT int NULL

MSSQL: Does the column exits in a table

select * from sys.columns
         where
Name = N'[column name]'
and Object_ID = Object_ID(N'[Table Name]')

List all the function name for a database

connect to any database.
Use [Database name]


SELECT name AS function_name,type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'

MSSQL: List all table size in a database

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

MSSQL: Creating Link Database Server

You can create any link database server with any provider string.

EXEC master.dbo.sp_addlinkedserver

    @server = N'LZAMSMEXPRD\AMSMEX',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

    @provstr=N'PROVIDER=SQLOLEDB;SERVER=lzamsmexqat\amsmex,1433'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LZAMSMEXPRD\AMSMEX',

    @useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='P@ssword!'

Eariler version could have some issue with user having no sysadmin role. You can use the following one for that.

EXEC master.dbo.sp_addlinkedserver

    @server = N'CHRGBKP2NT',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

@datasrc='PSM-TESTSVR2'


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CHRGBKP2NT',
    @useself=N'False',@locallogin=NULL,@rmtuser=N'jcolton',@rmtpassword='jcolton254'


For SQL Server 2012, use the following script.

EXEC master.dbo.sp_addlinkedserver @server = N'TOCGJTIM1PV\JTIME', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @datasrc=N'TOCFJTIM1UV\JTIME'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TOCGJTIM1PV\JTIME',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

List MSSQL Database size

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN
   (
   SELECT database_id, SUM(size) RowSize FROM sys.master_files
   WHERE type = 0
   GROUP BY database_id, type
   ) mfrows
   ON mfrows.database_id = db.database_id
   LEFT JOIN
  (
  SELECT database_id, SUM(size) LogSize FROM sys.master_files
  WHERE type = 1
  GROUP BY database_id, type
   ) mflog
  ON mflog.database_id = db.database_id
  LEFT JOIN
  (
  SELECT database_id, SUM(size) StreamSize
  FROM sys.master_files
  WHERE type = 2
  GROUP BY database_id, type
  ) mfstream
  ON mfstream.database_id = db.database_id
  LEFT JOIN
  (
   SELECT database_id, SUM(size) TextIndexSize
   FROM sys.master_files
   WHERE type = 4
   GROUP BY database_id, type
   ) mftext
   ON mftext.database_id = db.database_id