SQL fast tip

2008-10-21

1. Get total rows of table:
SELECT count(*) FROM <table_name>

2. Get total rows having field value null:
SELECT count(*) FROM <table_name> where <field_name> is null

3. Get column names of table:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = <table_name>

4. Get all rows of table:
SELECT * from <table_name>

5. Delete all rows of table (USE WITH CAUTION):
delete from <table_name>

6. Delete table (USE WITH CAUTION):
drop table <table_name>

7. Get reasons of job failure of SQL Agent jobs:
select * from MSDB.dbo.sysjobhistory

8. to search all tables to find a column or field name in a database

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE syscolumns.name=’YourColumnName”

Rating: 6.6/10 (9 votes cast)

Temporary Tables vs. Table Variables in SQL Server When writing T-SQL code, you often need a table in...
What are the difference between DDL, DML and DCL commands? | Oracle FAQ What are the difference between DDL, DML and DCL commands?...
MS SQL Date format Execute the following Microsoft SQL Server T-SQL datetime and date...
健康换算 Body Mass Enter your weight in kilograms and your height in centimeters...
tattertools中间category显示错误的修复发现了，确实是一个bug，你不信可以看你自己的源程序，错误是在这个函数。getEntriesWithPagingByCategory在文件blogcategoryindex.php里面在那句sql里面你可以看到$sql="SELECT e.*, c.label AS categoryLabel FROM {$database['prefix']}Entries...

Tags: code, sql, tips
Filed under 日新月异::Tech | Trackback | Comments RSS | Add Comment