I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)? 23 s 23 You can obtain this information and much, much more by querying … Read more
Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints? 17 s 17 If you want to disable all constraints in the database just run this code: — disable all constraints EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all” To switch them … Read more
I want to update a column in a table making a join on other table e.g.: UPDATE table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= a.commonfield AND a.BatchNO = ‘110’ But it is complaining : Msg 170, Level 15, State 1, Line 2 Line 2: … Read more
I’ve been learning Functions and Stored Procedure for quite a while but I don’t know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that. Can some one tell me why? 2Best Answer 21 The difference between SP and UDF … Read more
We all know that to select all columns from a table, we can use SELECT * FROM tableA Is there a way to exclude column(s) from a table without specifying all the columns? SELECT * [except columnA] FROM tableA The only way that I know is to manually specify all the columns and exclude the … Read more
What is the best way to get the names of all of the tables in a specific database on SQL Server? 19 s 19 SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or 2019: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’ To show only tables from a particular database SELECT TABLE_NAME FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES WHERE … Read more
What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I also know that CROSS APPLY doesn’t require a UDF as the right-table. In most INNER … Read more
I am trying to insert some text data into a table in SQL Server 9. The text includes a single quote ‘. How do I escape that? I tried using two single quotes, but it threw me some errors. eg. insert into my_table values(‘hi, my name”s tim.’); 1 14
What is the best way to get IDENTITY of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don’t understand the pros and cons attached to each. Can someone please explain the differences and when I should be using each? 1 14
I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved. I am looking for the specific SQL syntax to alter a … Read more