SQL statement Handy SQL statement-for your familiarity with unfamiliar databases

These days to learn the company's system data flow. It mainly involves several tables of data. However, the data in the table is millions, so if there is no conditional SQL statement, it will cause the query card to die, and even a memory overflow situation. The database used by the company is SQL Server, so the following SQL statements are mainly for SQL Server databases, and the other databases are similar.

1, when I want to be familiar with the database table structure, I need to execute a statement, this statement can see a lot of database information, such as column name, field name, field size, field type, etc...

SELECT * from information_schema.columns where table_name= ' table name '

2. And, of course, if you want to create a database table, you can execute the following statement that calls the stored procedure:

EXEC sp_help ' table name '

3, when I want to understand the database master, foreign key, I pass the following statement:

Query primary key: EXEC sp_pkeys @table_name = ' table name '

Query foreign Key:

Select primary key Column name = (selectname from syscolumns where colid=b.rkey and id = b.rkeyid), foreign key table name =object_name (B.fkeyid), foreign key Column name = (select n Ame from syscolumns wherecolid=b.fkey and id = B.fkeyid) from    Sysobjectsa join Sysforeignkeys b on a.id = B.constid Jo In sysobjects C on a.parent_obj =c.id where a.xtype = ' f ' and c.xtype= ' U ' and object_name (b.rkeyid) = ' table name '

4. Locate the data in front of the database using the TOP keyword. Because the amount of data is too large, so to analyze the data, we can extract data from the first 50 or 100 data to find, specific use method

Select Top100 * FROM table name

The ps:* can be replaced with the column name of the database, followed by a query condition, and so on.

5, when looking at the company field, a lot of fields are using subqueries, connection query, so it will not be able to check the relevant information (understand the difference between inner/left/right join)

6, the company database design thinking, because of the confidentiality of the relationship, so a lot of details can not be described, but we design the table, the relationship between the foreign key and table and the relations between the table must be clear, when necessary, you can draw draft auxiliary ideas.

Others, and then gradually summed up the problems encountered.

(Reproduced this site article please indicate the author and source Coder the extraordinary , do not use for any commercial use)

SQL statement Handy SQL statement-for your familiarity with unfamiliar databases