How to find columns with/without default values in a table

How to find the default values of all the columns in all the tables in a database.


SELECT SO.name AS [Table Name],SC.name AS [Column Name],SM.TEXT AS [Default Value],SC.colorder AS [Column Order]
FROM sysobjects SO
INNER JOIN sys.syscolumns SC
ON SO.id = SC.id
INNER JOIN sys.syscomments SM
ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
UNION
SELECT SO.name AS [Table Name],SC.name AS [Column Name],'' AS [Default Value],SC.colorder AS [Column Order]
FROM sysobjects SO
INNER JOIN sys.syscolumns SC
ON SO.id = SC.id
LEFT JOIN sys.syscomments SM
ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
AND SM.id IS NULL
ORDER BY SO.name,SC.colorder


How to find only the columns which contains default value in all the tables in a given database.

SELECT SO.name AS [Table Name],SC.name AS [Column Name],SM.TEXT AS [Default Value],SC.colorder AS [Column Order]
FROM sysobjects SO
INNER JOIN sys.syscolumns SC
ON SO.id = SC.id
INNER JOIN sys.syscomments SM
ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
ORDER BY SO.name,SC.colorder


How to find the columns and tables does not contain default values in a database.

SELECT SO.name AS [Table Name],SC.name AS [Column Name],SC.colorder AS [Column Order]
FROM sysobjects SO
INNER JOIN sys.syscolumns SC
ON SO.id = SC.id
LEFT JOIN sys.syscomments SM
ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
AND SM.id IS NULL
ORDER BY SO.name,SC.colorder


How to list all Sql Server "Server Name's" using C#.Net

using System.Data.Sql;

class Program
{
static void Main()
{
// Retrieve the enumerator instance and then the data.
SqlDataSourceEnumerator instance =
SqlDataSourceEnumerator.Instance;
System.Data.DataTable table = instance.GetDataSources();

// Display the contents of the table.
DisplayData(table);

Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}

private static void DisplayData(System.Data.DataTable table)
{
foreach (System.Data.DataRow row in table.Rows)
{
foreach (System.Data.DataColumn col in table.Columns)
{
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}
}
}

How to list Database names in Sql Server

To Get all the list of DataBase names in SqlServer, Execute the Stored Procedure "sp_databases"

sp_databases

How to know what are the triggers present in the Database

Execute the following statements in Sql Server Management Studio.


SELECT * FROM SYSOBJECTS
WHERE XType = 'tr'

To view the trigger code execute the following statement. Replace the <Trigger_Name> with actual trigger name


SP_HELPTEXT <Trigger_Name>


How to enable CLR in SqlServer

To use CLR(.Net) defined objects in SQL Server. We need to enable CLR functionality in SQL Server.

To enable CLR functionality in SQL Server, execute the following command in Sql Server Management Studio.

sp_configure 'clr enabled', 1
GO
Reconfigure
GO