CODEWITHSUNDEEP
sql-serverlookup-tables
Joe
Joe

Reputation: 1055

generate dropdown list for sql server db management

I need to build a web page to allow people to directly manage database tables: add and delete records, modify fields values and so on

Many fields in most tables do refers to values in other tables (although not all have FK constraint) and therefore for this fields I need to show a dropdown list of the acceptable values.

With my poor skills the only solution I've been able to find is to to create a new table where I would have listed all fields of all table and set for each field the adequate query: something like this:

    TableName   ColField    LookupQuery
    Sales       UserID      Select UserID from Users where Users.Active='1'

and so on

But I'm wondering... is there a (much) better approach to achieve same results?

if not..

how can I programmatically create a table that list all existing fields of all tables so I only need to add the lookupquery Field? now I use 

insert into Tables (IDF,TableName,ColName,DataType,MaxLen) 
SELECT T.Name+C.Name,  T.NAME , C.NAME , P.NAME , P.MAX_LENGTH
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE' and P.Name <>'sysname' and c.name<>'ID';

but this add every time all the tables and columns.. I tried to add some constraint but execution stops immediately.. while tried to add some "EXCEPT....." but do not find yet the working solution..

therefore I can use it only first time and then add extra rows manually..

Thanks Sergio

Upvotes: 0

Views: 1670

Answers (1)

Code Different
Code Different

Reputation: 93161

You were close, only need to add a LEFT JOIN to exclude tables & columns that already exist in Tables already:

INSERT INTO Tables
(
    IDF,
    TableName,
    ColName,
    DataType,
    MaxLen
)
SELECT T.Name+C.Name,  T.NAME , C.NAME , P.NAME , P.MAX_LENGTH
FROM SYS.OBJECTS       AS T
INNER JOIN SYS.COLUMNS AS C ON T.OBJECT_ID=C.OBJECT_ID
INNER JOIN SYS.TYPES   AS P ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
LEFT JOIN dbo.Tables   AS X ON T.NAME = X.TableName AND C.NAME = X.ColName
WHERE T.TYPE_DESC='USER_TABLE' and P.Name <>'sysname' and c.name<>'ID'
    and X.IDF IS NULL;

Upvotes: 1

Related Questions