列出架构表，然后显示选定的表数据 [英] list schema tables then display selected table data

问题描述

大家好，请帮助我

我想列出架构表并选择任何表以显示其数据，并且可以编辑和删除所选表中的数据

thanx

hi all,, plz help me

i want to list schema tables and select any table to display its data and can edit and delete data from selected table

thanx

推荐答案

您可以使用以下查询选择并显示数据库的表名

You can use the following query to select and display the table names of the database

-- This is for SQL Server Database
SELECT *
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'

获取表列表后，可以将选定的表名传递给以下查询以获取记录列表.

Once you get the lists of table you can pass the selected table name to the following query to get the list of records.

SELECT *
FROM TableName -- TableName will be one of the tables from the list. E.g Customers

delete和update命令有些棘手.您可能需要知道表的主键.但是，更新还取决于要更新的​​列.
这是仅列出表的主键列的方法.

The delete and update commands are a little bit tricky. You may need to know the primary key of the table.However the update also depends on which columns are going to be updated.
Here is how to list only the primary key columns of the table.

Exec sp_primarykey TableName -- TableName will be one of the tables from the list. E.g Customers

这是列出表中所有列的方法.

Here is how to list all columns of the table.

SELECT 
* FROM sys.columns WHERE object_id = OBJECT_ID(TableName) -- TableName will be one of the tables from the list. E.g Customers

一旦获得主键和其余列的列表，您将构建where语句字符串
对于删除删除SQL语句 [

Once you get the list of primary key and the rest of columns you will build your where statement string
For Delete Delete SQL Statement[^]

DELETE 
FROM TableName 
WHERE Criteria -- Criteria depends of the primary key and the corresponding records E.g pk1='1' AND pk2='2'

对于更新更新SQL语句 [

For Update Update SQL Statement[^]

UPDATE TableName
SET ColumnsList -- Columnslist with corresponding list values col1='1' ,col2='2' ...
WHERE Criteria -- Criteria depends of the primary key and the corresponding selected record E.g pk1='1' AND pk2='2'

这基本上就是您需要做的.可能几乎没有其他工作和调整.

This is basically what you need to do. There may be little additional work and tune ups.

这篇关于列出架构表，然后显示选定的表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！