SELECT DISTINCT schema_name(fk_tab.schema_id) + ‘.’ + fk_tab.name as foreign_table, ‘>-‘ as rel, schema_name(pk_tab.schema_id) + ‘.’ + pk_tab.name as primary_table FROM sys.foreign_keys fk INNER JOIN sys.tables fk_tab on fk_tab.object_id = fk.parent_object_id INNER JOIN sys.tables pk_tab on pk_tab.object_id = fk.referenced_object_id WHERE pk_tab.[name] = ‘Your table’ — enter table name here — and schema_name(pk_tab.schema_id) = ‘Your table schema […]
Tag: column
How to insert results of a stored procedure into a temporary table
CREATE TABLE #tmpTable ( COL1 int, COL2 int, COL3 nvarchar(max), COL4 nvarchar(max), COL5 bit ) INSERT INTO #tmpTable exec SpGetRecords ‘Params’ NOTE: The columns of #tmpTable must be the same as SpGetRecords. Otherwise, there will be a problem. If there are no parameters in the stored procedure, you don’t need to use ‘Params’.
How to use OUTPUT for Insert, Update and Delete in SQL
The OUTPUT clause was introduced in SQL Server 2005. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE, or DELETE statement. It even supports a MERGE statement, which was introduced in SQL Server 2008 version. The OUTPUT clause has access to two temporary or in-memory SQL tables, INSERTED […]
How to get difference between 2 tables in MSSQL
If you have two tables A and B, both with column C, here are the records, which are present in the table A but not in B: SELECT A.* FROM A LEFT JOIN B ON (A.C = B.C) WHERE B.C IS NULL To get all the differences with a single query, a full join must be used, like this: […]
How to create a single string from multiple rows in T-SQL and MySQL
To create a single string from multiple rows in MySQL, you can use the GROUP_CONCAT function. This function allows you to concatenate values from multiple rows into a single string, separated by a specified delimiter. Here is an example of how you can use this function: SELECT GROUP_CONCAT(column_name ORDER BY column_name ASC SEPARATOR ‘,’) FROM […]
How to add default value for Entity Framework migrations for DateTime and Bool
Just add defaultValue parameter in CreateTable method for property: public partial class TestSimpleEntity : DbMigration { public override void Up() { CreateTable( “dbo.SimpleEntities”, c => new { id = c.Long(nullable: false, identity: true), name = c.String(), deleted = c.Boolean(nullable: false, defaultValue: true), }) .PrimaryKey(t => t.id); } public override void Down() { DropTable(“dbo.SimpleEntities”); } } After that, run update-database -verbose command, […]
Add default value to Devex Grid Columns in C#
protected void grid_InitNewRow(object sender, DevExpress.Web.Data.ASPxDataInitNewRowEventArgs e) { e.NewValues[“BirthDate”] = new DateTime(1970, 1, 10); e.NewValues[“Title”] = “Sales Representative”; } Here is the result:
How to check for ‘IS NOT NULL’ And ‘IS NOT EMPTY’ string in SQL
If you only want to match N” as an empty string SELECT COLUMN FROM TABLE WHERE DATALENGTH(COLUMN) > 0 If you want to count any string consisting entirely of spaces as empty SELECT COLUMN FROM TABLE WHERE COLUMN <> ” If you want to use DATALENGTH for any string consisting entirely of spaces then you […]
Filter Rows By Max Date in SQL
SELECT m1.Column1, m1.Column2, m1.Column3, r.MaxTime FROM ( SELECT Column1, MAX(TimeColumn) as MaxTime FROM MyTable GROUP BY Column1 ) r INNER JOIN MyTable m1 ON m1.Column1= r.Column1AND m1.TimeColumn = r.MaxTime
How to use column search in datatable when responsive is false
just add “$(“th”).show();” in the end of initComplete … , initComplete: function () { … $(“th”).show(); } , …
Unique Constraints in MS SQL Server to prevent duplications
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint […]
Rename column SQL Server
EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’
How to Alter a Computed Column in SQL?
There is NO way to alter computed column. You have to drop and recreate it. Let’s say you have a table with 3 column. And the 3rd one is the sum of the other two: CREATE TABLE MyTable (Column1 int, Column2 int, Column3 AS Column1 + Column2); If you want to change that computed column as multiply of the other […]
How to set Tooltip for a particular Devexpress Gridview Column
You can use code below:
Devexpress ASPxGridview Column Grouping in Code
The following code shows how to group by two columns (“Country” and “City”).
Add Default Value in SQL Server
If there is no default value for the column then you can use this query: ALTER TABLE MyTable ADD CONSTRAINT df_MyTable_MyColumn DEFAULT 0 FOR MyColumn
Hide GridView Column on server-side
protected void GridView_RowDataBound(object sender, GridViewRowEventArgs e) { e.Row.Cells[index].Visible = false; }